Referencing the same cell on multiple sheets

[danc]

I have workbook that contains many sheets in the same format and a cover
sheet to summarize the workbook. My problem is in extending a formula on the
coversheet.

For example Sheet 1 is the coversheet. Sheets 2 through 100 are different
people, say Adam, Bill, Carl, Dave, Evan, Frank.... On sheet "Adam", it
shows how many apples he started with, how many remaining, and the size of
apples. Each Sheet has this in the same spot. I want the coversheet to
summarize all the other sheets, with a column for the Name, Apples started
with, Apples remaining, and Size of Apple for each sheet (Adam, Bill,
Dave......) .

It is easy to make a formula that referencing cells on Adam's sheet. My
problem is extending the formula down, locking the cell, and only changing
the sheet name. Whenever I try, excel keeps the sheetname and only changes
the cell.
I have many, many different sheets to summarize, and haven't found a better
way than manually typing each one.

 

[Pete_UK]

If you have a list of names on your cover sheet which is the same as
the list of sheet names, then you can do it like this:

=INDIRECT("'"&$B2&"'!A5")

where B2 (and below) contains the names, like Adam, Bill etc and cell
A5 is the cell in the other sheets where you want to get the data
from. As you copy this down B2 will change, but A5 will not as it is
within quotes. Note that the first bit is
<quotes><apostrophe><quotes>, and there is also an apostrophe
immediately before the !, in case your names have spaces in them.

Just copy this down as required. You can also copy it across, but you
will need to manually change the A5 to B5 (or whatever).

Hope this helps.

Pete