HELP QUICK I NEED TO KNOW HOW TO ...

  • Thread starter Kai Jam via OfficeKB.com
  • Start date
K

Kai Jam via OfficeKB.com

I have created 100 account activity worksheets for each account holder. I
would like to know how to transfer 3 columns to one worksheet in a list.

Example:
Worksheet 1 for Farmer X

Date: Reference: Check No: Amount:

(Would like to transfer Date, Check No and Amount to...)

Worksheet 1 Check Register

Date: Check No: Amount:

The issue is that I need to do this for all 100 accounts. if I just say
=A1, then each of the 100 reports to that column and it keeps replacing. I
don´t have much time to produce this.. HELP!!
 
A

Anki

Try to build a Pivot table to consolidate your 100 sheets by...
1) Go to data, pivot table reports, select multiple consolidation ranges
2) select the range and add...(I haven't tried it but you may want to try
choosing sheet1!A:C if the number of rows in each sheet is different...that
would save your some pain).
3) Keep adding till you are done with all 100, then press next
4) Pivot table should have all 3 columns from each of the 100 sheets. If you
select the full collumn, the pivot report will show a line called 'blank'
5) the page filter allows you to screen by each client (indeed it refers to
item1, item2 by the order you enter the range). If you want to have the name
(e.g. FarmerX), you need to add a column in each sheet with name

Hope it helps~
 
M

Max

Not sure but this formulas approach might hopefully provide some ideas to
help you along a little ..

Assuming the sheets are named: Farmer1, Farmer2, Farmer3, ... Farmer100
and are identically structured, with data within A1:D10 (i.e. max 10 rows
per table), e.g.:

In sheet: Farmer1
Date: Reference: Check No: Amount:
01-May-05 1111 2222 100
02-May-05 1112 3333 200
03-May-05 1113 4444 300
etc

In sheet: Farmer2
Date: Reference: Check No: Amount:
01-May-05 2111 2222 100
02-May-05 2112 3333 200
03-May-05 2113 4444 300
etc

In a new sheet, named as say: Summary

Put in A1:

=OFFSET(INDIRECT("Farmer"&INT((ROWS($A$1:A1)-1)/10)+1&"!$A$1"),MOD(ROWS($A$1
:A1)-1,10),COLUMNS($A$1:A1)-1)

Copy A1 across to D1, fill down to D1000
(and then format the cols accordingly)

For a cleaner look, if desired, suppress extraneous zeros from display in
the sheet via: Tools > Options > View tab > Uncheck "Zero values" > OK

The output in Summary will look like:

Date: Reference: Check No: Amount:
01-May-05 1111 2222 100
02-May-05 1112 3333 200
03-May-05 1113 4444 300
....
....
Date: Reference: Check No: Amount:
01-May-05 2111 2222 100
02-May-05 2112 3333 200
03-May-05 2113 4444 300
....
....

Adapt to suit: If the max rows per table/sheet to be extracted is say 20,
just change the number "10" within both the INT(...) and MOD(...) parts to
"20" in the formula in A1, then copy A1 across to D1 and fill down to D2000
(instead of D1000). Filling down extent is : = # of rows per sheet x # of
sheets (viz.: = 20 rows per sheet x 100 sheets = 2000 rows to fill down).
 
Top