Hi Titam,

I just realised it can be reduced from using two columns to just one:

Sheet1!A2 has the following formula

=INDIRECT("Sheet" & ROW() & "!"& $A$1)

Sheet1!A1 still contains the address of the text cell that is in the
same position on every sheet.

The only drawback I can see is that when the common text cell is a
blank cell the summary on sheet1 will be a "0" (zero) instead of blank.

Ken Johnson