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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ali" wrote:

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