Dragging a formula linked to another sheet

[golfwannabe]

I am setting up a summary sheet that brings dollar totals from five columns from 100 diffrent sheets to this one summary sheet. I understand how to use the $ sign for the absolute and relative cell references so that when I drag down the summary sheet, the cell references increase incrementally. What I don't know how to do is have the sheet references increase incrementally in the same way. I don't want to have to manually edit each cell's reference to a particular sheet since I've got 100 sheets that I am summarizing onto one sheet. Thanks for your help.

I've attached a .pdf of the sheet that I am working with.

[Huron]

In your example you can change the formula in cell D10 to

=INDIRECT("'#"&(ROW()-7)&"'!G43")

This can then be dragged down and will increment the sheet names #4, #5 etc

[golfwannabe]

Thanks for providing this. I've never used the INDIRECT function before so it may take me a little to know how to utilize it beyond what you've given me. I'll try it tonight and let you know how it worked. Again...Thanks!

[golfwannabe]

I thought I could get this, but I need a little clarification.

# refers to what?
Row()-7 refers to what?
!G43 is the cell I am pulling from.

I can see that using the INDIRECT function will be really helpful, but it's very different from how I've used Excel in the past. Thanks for your help!

[Huron]

Your existing formula in D10 is
='#3'!G43
so I'm assuming that your sheets are named #3, #4, #5, etc

In the formula I gave you
=INDIRECT("'#"&(ROW()-7)&"'!G43")

The INDIRECT function allows a text string to be used as a cell reference so the part in brackets is all the text from your formula except the number in the middle which you want to increment when the formula is dragged. Row() returns the number of the current row so in D10 it will return 10 and you need to subtract 7 to obtain 3. In D11 it will return 11 less 7 to obtain 4 etc

[golfwannabe]

Thanks. This is a huge help. I appreciate you taking the time to explain it.