Summary of multiple spreadsheets:

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a workbook with multiple spreadsheets. I want to summarize the
information from the same selected cell on each sheet on to a summary sheet.
I am not trying to sum the totals.

Therefore on the summary sheet I reference a cell say A1 on Sheet 1 using =.

What I would like to do then is drag and copy down the column on the summary
sheet so that it takes the info from cell A1 off each consecutive sheet and
places it on to the summary sheet i.e.

Sheet 1 $A$1
Sheet 2 $A$1
Sheet 3 $A$1

And so on for all the sheets in the work book.

But when I copy the formula down the column I can’t get the sheet number to
update to the next sheet i.e. it copies as follows:

Sheet 1 $A$1
Sheet 1 $A$1
Sheet 1 $A$1

I then have to manually change the sheet number in each cell.

Is there any way to copy so that the sheet number updates to the next sheet
in the work book.

Thankyou in anticipation
 
If indeed you are using the default sheet names it is easy

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

will return what's in A1 in Sheet1:whatever sheet you have

of course if you have totally unique names for each sheet as opposed to the
same textstring + number
it becomes much harder



--
Regards,

Peo Sjoblom

(No private emails please)
 
Thanks very much that worked fine
--
Peter S


Peo Sjoblom said:
If indeed you are using the default sheet names it is easy

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

will return what's in A1 in Sheet1:whatever sheet you have

of course if you have totally unique names for each sheet as opposed to the
same textstring + number
it becomes much harder



--
Regards,

Peo Sjoblom

(No private emails please)
Click to expand...
 
Back
Top