Multiple Sheets

[Guest]

I have multiple worksheets in a workbook. I would like to copy the same
row/cell from each worksheet to a matching column on the main worksheet.
(ie: The summary/main page has a column listing the names of the worksheets.
In adjacent column, we want to copy a particular cell for the corresponding
sheet) I need to "lock" the cell and have the worksheet title as my
variable. Can this be done so we don't have to enter ='sheetname'$cell for
each?

 

[Otto Moehrbach]

What do you mean by "I need to "lock" the cell and have the worksheet title
as my
variable."? What cell and what does "lock" mean to you? HTH Otto

 

[Bernie Deitrick]

=INDIRECT("'" & $A2 & "'!H8")

With your sheet names starting in A2 and extending down column A, to extract cell H8 from each
sheet.

Or, if you want to put the sheet address as a string in another cell, say B1

=INDIRECT("'" & $A2 & "'!" & $B$1)

Copy either down to match your list, with H8 entered into cell B1.

HTH,
Bernie
MS Excel MVP

 

[Guest]

I have input the ='sheetname'!cell on the summary page. I would like to copy
this formula, with the sheetname being my "variable" and the cell info locked
so it remains the same for each sheet. I know how to lock the cell info so
it remains constant. Can I enter a formula that I can copy, down a column on
my main summary page, that the 'sheetname' changes, but the cell on each
sheet remains constant? Make sense?

 

[Guest]

In the workbook I have the following sheets, in the following order:
Summary, Atlanta, Charlotte, Texas, and Tenn. On the Summary sheet, in
column d, I have all of the sheet names listed in their exact order (In cell
D3 is "Atlanta", cell D4 "Charlotte", Cell D5 "Texas", etc..)

In column F, I want to place, in each corresponding row, a value from the
corresponding sheet (In cell F3 I want the value ='Atlanta'!H10; cell F4 I
want the value ='Charlotte'!H10; cell F5 I want the value ='Texas'!H10,
etc...)

Is there a formula that I can enter in the top cell of the Summary sheet
(cell F3) that I can simply copy down the F row of this Summary sheet, where
the sheet name changes?

 

[Bernie Deitrick]

In cell F3:

=INDIRECT("'" & $D3 & "'!H10")

and copy down...

HTH,
Bernie
MS Excel MVP