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 cant 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

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" wrote in message
...
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 cant 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

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" wrote in message
...
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 cant 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