change formula- keep cell increment sheet numbers

[Ali]

Hi there, i have a workbook consisting of approx 70 sheets, named 1 - 70.
One sheet has formuals that read cells from all the others sheets.
Example
C1 = '1'!$A$4
C2 = '2'!$A$4
C3 = '3'!$A$4

I need to drag the formula down from C1:C70, but can't find a way that it
increments sheet numbers. It is taking for-ever to cut and copy, and then
change the sheet numbers individually. This sheet also has formulas reading
C1:C70 all the way through to AK1:AK70 so too many cells to copy.
Any ideas on how to increment sheet numbers each row down.
many thanks
Ali

 

[Luke M]

If your sheet names are simply numbers, you could do:
=INDIRECT("'"&ROW(A1)&"'!A4")
Copying this down will cause the reference to change to A2, A3, etc, thus
changing your sheet. Note that since the A4 is inside quotes (is text in the
formula), it never changes so there is no need for absolute reference on that
part.

 

[Ali]

Hi Luke, this all works fine if I start a new sheet with this formula.
problem is my info in-correct. my actual sheet starts on
C8 = '1'!$A$4Not sure how to do this as it is not starting on Line 1/

SECONDLY, the sheets are presently set up and named as 1,2,3,4, etc. So
your formula works fine. however, once the sheets have been re-used they
will be re-named as guests name
1 might become Keith
2 might become James
3 might become Jones
once names are changed the formula then gives a Ref error.

Any ideas, thanks

 

[Ali]

Hi Luke, this all works fine if I start a new sheet with this formula.
problem is my info in-correct. my actual sheet starts on
C8 = '1'!$A$4Not sure how to do this as it is not starting on Line 1 and it needs to for
formula to work

SECONDLY, the sheets are presently set up and named as 1,2,3,4, etc. So
your formula works fine. however, once the sheets have been used they
will be re-named as guests name
1 might become Keith
2 might become James
3 might become Jones
once names are changed the formula then gives a Ref error.

Any ideas, thanks