Try it this way, then:

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

ROW(A1) will return 1, so the text in the bracket will evaluate to
Sheet1!C5 and the INDIRECT function will bring the data from that
calculated cell. When the formula is copied down it will change to
ROW(A2) which will return 2, so the text becomes Sheet2!C5, then in
the next row it will be Sheet3!C5, and so on.

This relies on your sheets being named as Sheet1, Sheet2, Sheet3 etc -
the #REF error implies that they are not.

Hope this helps.

Pete

On Oct 3, 10:36*am, Louise wrote:
Hi Pete,

Thanx so much for that but it does not seem to work. It gives me #REF error.
I checked to see the explanation for the INDIRECT formula but honestly do not
understand what they are saying. I should mention that the cell i want from
Sheet 1 will not be in the same cell on the Summary sheet. I was just using
an example but in actual fact it will be A10=Sheet1!C5, maybe this is why
this formula does not work. Can you help?

Louise



"Pete_UK" wrote:
Try this in A1:

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

then you can copy this down as required.

Hope this helps.

Pete

On Oct 3, 9:33 am, Louise wrote:
I have created a sheet and duplicated this sheet on multiple sheets as it is
a standard form that needs to be completed for payment requests. I then
created another sheet in the same spreadsheet where i want to summarize all
these individual sheets, meaning i want to extract exactly the same
information from each sheet into the summary sheet. I have set up the first
line by saying for instance A1=Sheet1A1 now how do i copy this formula down
so that the next formula is A2=Sheet2A1 and so forth. This must be possible
its such a simple thing but i cannot seems to find it on the help menu.
Anybody out there that can help.

Thanx
Louise- Hide quoted text -

- Show quoted text -