Linking Worksheets to a Summary Worksheet

[Nell]

Hi,

I am working on a large budget file which has 150 worksheets - one worksheet
for each general ledger number and each worksheet shows January-December
expenses. January thru December are listed in each row so my cells I want to
copy (to my summary sheet) for example, January would be C4, February would
be C5, etc. My summary sheet has January - December columns across the top
and each row is the g/l numbers. I want to be able to copy C4, C5, etc. to
C4, D4, on the summary sheets and use the fill feature but I can't get it to
fill properly. If I use transpose, it doesn't copy the formula. I want to
be able to change the worksheets and have it automatically change the summary
sheet. I really appreciate your help.

Nell

 

[Max]

One way

In your summary sheet,
Assume g/l numbers (the sheetnames) are listed in A2 down
Assume A2 contains say, the g/l number: 111

Put in B2:
=INDEX(INDIRECT("'"&$A2&"'!C:C"),COLUMNS($A:A)+3)

B2 returns the fig in C4 (January's fig) from the sheet: 111
Copy B2 across by 12 cols to return the col figs for Jan-Dec in sheet: 111
(in C4:C15) into B2:M2, then fill down as far as required (by 150 rows) to
return likewise from all the 150 g/l sheets.