software

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

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

=INDIRECT(quot;'Sheetquot;amp;ROWS($A$1:A1)amp;quot;'!A1quot;)

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)quot;Peterquot; gt; wrote in message
...
gt;I have a workbook with multiple spreadsheets. I want to summarize the
gt; information from the same selected cell on each sheet on to a summary
gt; sheet.
gt; I am not trying to sum the totals.
gt;
gt; Therefore on the summary sheet I reference a cell say A1 on Sheet 1 using
gt; =.
gt;
gt; What I would like to do then is drag and copy down the column on the
gt; summary
gt; sheet so that it takes the info from cell A1 off each consecutive sheet
gt; and
gt; places it on to the summary sheet i.e.
gt;
gt; Sheet 1 $A$1
gt; Sheet 2 $A$1
gt; Sheet 3 $A$1
gt;
gt; And so on for all the sheets in the work book.
gt;
gt; But when I copy the formula down the column I can’t get the sheet number
gt; to
gt; update to the next sheet i.e. it copies as follows:
gt;
gt; Sheet 1 $A$1
gt; Sheet 1 $A$1
gt; Sheet 1 $A$1
gt;
gt; I then have to manually change the sheet number in each cell.
gt;
gt; Is there any way to copy so that the sheet number updates to the next
gt; sheet
gt; in the work book.
gt;
gt; Thankyou in anticipation
gt;
gt; --
gt; Peter SThanks very much that worked fine
--
Peter Squot;Peo Sjoblomquot; wrote:

gt; If indeed you are using the default sheet names it is easy
gt;
gt; =INDIRECT(quot;'Sheetquot;amp;ROWS($A$1:A1)amp;quot;'!A1quot;)
gt;
gt; will return what's in A1 in Sheet1:whatever sheet you have
gt;
gt; of course if you have totally unique names for each sheet as opposed to the
gt; same textstring number
gt; it becomes much harder
gt;
gt;
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; (No private emails please)
gt;
gt;
gt; quot;Peterquot; gt; wrote in message
gt; ...
gt; gt;I have a workbook with multiple spreadsheets. I want to summarize the
gt; gt; information from the same selected cell on each sheet on to a summary
gt; gt; sheet.
gt; gt; I am not trying to sum the totals.
gt; gt;
gt; gt; Therefore on the summary sheet I reference a cell say A1 on Sheet 1 using
gt; gt; =.
gt; gt;
gt; gt; What I would like to do then is drag and copy down the column on the
gt; gt; summary
gt; gt; sheet so that it takes the info from cell A1 off each consecutive sheet
gt; gt; and
gt; gt; places it on to the summary sheet i.e.
gt; gt;
gt; gt; Sheet 1 $A$1
gt; gt; Sheet 2 $A$1
gt; gt; Sheet 3 $A$1
gt; gt;
gt; gt; And so on for all the sheets in the work book.
gt; gt;
gt; gt; But when I copy the formula down the column I can’t get the sheet number
gt; gt; to
gt; gt; update to the next sheet i.e. it copies as follows:
gt; gt;
gt; gt; Sheet 1 $A$1
gt; gt; Sheet 1 $A$1
gt; gt; Sheet 1 $A$1
gt; gt;
gt; gt; I then have to manually change the sheet number in each cell.
gt; gt;
gt; gt; Is there any way to copy so that the sheet number updates to the next
gt; gt; sheet
gt; gt; in the work book.
gt; gt;
gt; gt; Thankyou in anticipation
gt; gt;
gt; gt; --
gt; gt; Peter S
gt;
gt;