multiple sheet cells in one column

[Cyberbiker]

I have a multiple-sheet workbook, and on the final sheet, I need to take cell
A1 from each of the earlier sheets and make a column.

I know that say cell A1 on my final sheet should be "=,1,!A1", and cell A2
would be "=,2,!A1", etc. I have a pile of sheets where this needs to be done
multiple times. Is there any way to automate this? If I just pull down the
box, it advances the row number, and I'm wondering if there's a way to do it
so it advances the sheet number instead.

TIA!!

 

[Ragdyer]

Enter this anywhere, and copy down as needed:

=INDIRECT("'"&ROWS($1:1)&"'!A1")