View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
MartinW MartinW is offline
external usenet poster
 
Posts: 860
Default summary sheet that refers to multiple sheets

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