Wuuooo.... Now you've started one Biff.
I used to use ROWS() for this sort of function until
JEMcGimsey chided me that ROW() is far more economical.
He didn't fully explain why and by comparing how
they work I came to the conclusion that when they
are dragged down to say row 2000,

the ROW() function is still just asking what row am I in
Whilst the ROWS() function is calculating A1:A2000

I don't know if my conclusion is right, I suppose I
should have asked JE for his reasoning.

Can you shed any more light on this?

Regards
Martin

"T. Valko" wrote in message
...
If you were pasting it into say row 5 for example change it to
=INDIRECT("Sheet"&ROW()-4&"!A1")

Better if you "hardcode" the row:

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

That way you don't have to calculate any offset depending on what row you
enter the formula in and as an added bonus it's robust against row
insertions. If you inserted a new row above the row that contains your
formula, the formula will break.


--
Biff
Microsoft Excel MVP


"MartinW" wrote in message
...
Hi,

=INDIRECT("Sheet"&ROW()&"!A1")
This formula pasted into row 1 of your summary sheet and
dragged down will return
Sheet1 A1
Sheet2 A1
Sheet3 A1
etc.

If you were pasting it into say row 5 for example change it to
=INDIRECT("Sheet"&ROW()-4&"!A1")

Another method that might come in handy is Gord Dibben's
Start and End procedure.

Insert a blank worksheet before your first worksheet and
another after your last sheet and before your summary sheet.
Then in your summary sheet put this =SUM(Start:End!A1)

This will sum all of the A1s in your worksheets.

HTH
Martin



"TBA" wrote in message
...
We are trying to produce a summary sheet that displays the value of the
same
cell from a range of sheets. Ideally it will be a formula that can be
copied
down the column, with each cell incrementing to the next sheet.
possible solutions might be around the use of a reference list to change
the
sheet reference within the formula?apologies if this makes no sense!
happy to
elaborate further