Excel Worksheet Formula Question

T

TCIDawn

I know there is a simple formula for this, but I haven't had to wor
with this for so long, I just can't seem to pull it from my memory, an
I don't know the technical terms well enough to look it up in help.

I have 250 sheets in a workbook. I need to summarize the data from th
same cells in each sheet to a "cover" sheet.

I would like the name of each sheet in their own cell in the cove
sheet and the data of one or more cells from these sheets i
corresponding cells.

I tried the drill down, but wasn't exactly sure that was what I needed
and kept ending up with an error in the formula each time I tried t
name cells. ? (This is probably completely wrong anyway).

Thanks for any help. I feel like such a novice compared to th
complicated questions I have read
 
F

Frank Kabel

Hi
try

=SUM(INDIRECT("'" & A1 & "'!A1:B10"))

where A1 stores the name of your sheet
 
T

TCIDawn

Well Frank,

I need a bit of clarification please.

The A1 in your formula =SUM(INDIRECT("'" & A1 & "'!A1 :B10 "))
is the sheet name, correct?

Would the A1 be the beginning sheet name and the B10 be the endin
sheet name? Or would these fields be the cells within the sheets?

I have not been able to make any of these combinations work, so I gues
I am even more lost than I thought!
Thanks for your assistance.

Dawn :confused
 
F

Frank Kabel

Hi
in A1 simply place your sheet name. The right part of this formula
(!A1:B10) is the range on this references sheet.

So my example formula sums the range A1:B10 on the sheets you have
identified in cell A1. So if you for example enter the value
sheet_test
in cell A1 my formula would return the same result as
=SUM('sheet_test'!A1:B10)
 
Top