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.