View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Linking Worksheets to a Summary Worksheet

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nell" wrote:
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