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?

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

=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


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

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?

"Otto Moehrbach" wrote:

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

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" wrote:

=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


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

In cell F3:

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

and copy down...

HTH,
Bernie
MS Excel MVP


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

=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


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