Summary of multiple spreadsheets:

[Peter]

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

--
Peter S

[Peo Sjoblom]

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)


"Peter" <[email protected]> wrote in message
news:[email protected]...
>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
>
> --
> Peter S

[Peter]

Thanks very much that worked fine
--
Peter S


"Peo Sjoblom" wrote:

> 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)
>
>
> "Peter" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
> > --
> > Peter S
>
>