Excel question: progressing sheet number in formula

[ugaboy]

How do I progress a sheet number in a formula. For example if I would like to generate a list of the entries that are in the same cell in several worksheets. For the first sheet, I have typed =sheet1!A1. Then I would like to repeat this formula with the next cell =sheet2!A1 and so on. Is there any way to "automate" this? Any help is appeciated.

 

[Joe4]

WELCOME TO THE BOARD!

If you want row 1 to return the value from Sheet1, row 2 to return the value from Sheet2, etc., then enter this formula on row 1 and copy down:
=INDIRECT("Sheet" & ROW() & "!A1")

 

[ugaboy]

Thanks for your assistance...but

what I actually need is for the cell (row and column) to stay the same as I copy the formula down but I need the sheet # to progress by one each time. Any ideas?

 

[Joe4]

ugaboy,

Did you try my response? That is exactly what it does (note the "A1" is hardcoded in the formula).

What the ROW() function is doing is telling it which Sheet to look at. If the formula is entered on Row 1, the formula will look at Sheet1, if the formula is on row 2, it looks at Sheet2, etc. Hence, if you are copying your formula down the column, it will do exactly what you want it to.

Enter the function without the INDIRECT part, and you will see what it is returning.

="Sheet" & ROW() & "!A1"

You just need to use INDIRECT function when doing range references based on a formula.

 

[ugaboy]

I'm sorry

I misunderstood your original post. Sorry about that. I just tried your suggestion and it worked. Thanks so much, I really appreciate your help. Thanks again.