I have a simple formula which reads:

='P1'!$L$52

Where 'P1' is a worksheet name.

I want the value 'P1' to be the contents of another cell.

Reason being:

This worksheet is a summary worksheet. Each row contains multiple values
(or formulas) from a different worksheet. I'd like to have one column where I
type the worksheet name and have that name automatically inserted into the
formulas throughout the row.

I apologize if this has been asked a hundred times before, I just don't know
a good search string to enter!

Thanks for your help.

I'm not sure which cell contains the worksheet name - assume it is P1,
and then use this formula:

=INDIRECT("'"&P1&"'!$L$52")

Copy this down, and it will take the sheet name from P1, then P2, P3
etc. I've assumed that the worksheets are all in the same workbook.

Hope this helps.

Pete

On Feb 4, 1:53*pm, ptrip wrote:
I have a simple formula which reads:

='P1'!$L$52

Where 'P1' is a worksheet name.

I want the value 'P1' to be the contents of another cell.

Reason being:

This worksheet is a summary worksheet. *Each row contains multiple values
(or formulas) from a different worksheet. I'd like to have one column where I
type the worksheet name and have that name automatically inserted into the
formulas throughout the row.

I apologize if this has been asked a hundred times before, I just don't know
a good search string to enter!

Thanks for your help.

If the sheet name is in A1 then:
=INDIRECT(A1 & "!" & "$L$52")

--
Gary''s Student - gsnu200767

Use the ampersand to concatenate the values for Example
If you have selected column "C" to hold the worksheet name, and you still
want to show the value of 'P1'!$L$52 you would something like this:

='P1'!$L$52&$C1 and so on....

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"ptrip" wrote:

I have a simple formula which reads:

='P1'!$L$52

Where 'P1' is a worksheet name.

I want the value 'P1' to be the contents of another cell.

Reason being:

This worksheet is a summary worksheet. Each row contains multiple values
(or formulas) from a different worksheet. I'd like to have one column where I
type the worksheet name and have that name automatically inserted into the
formulas throughout the row.

I apologize if this has been asked a hundred times before, I just don't know
a good search string to enter!

Thanks for your help.

=INDIRECT("'"&A1&"'!$L$52")
that is
( double-quote single-quote &A1 double-quote single-quote !$L$52
double-quote )

With the text P1 in cell A1
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ptrip" wrote in message
...
I have a simple formula which reads:

='P1'!$L$52

Where 'P1' is a worksheet name.

I want the value 'P1' to be the contents of another cell.

Reason being:

This worksheet is a summary worksheet. Each row contains multiple values
(or formulas) from a different worksheet. I'd like to have one column
where I
type the worksheet name and have that name automatically inserted into the
formulas throughout the row.

I apologize if this has been asked a hundred times before, I just don't
know
a good search string to enter!

Thanks for your help.

Thank you, thank you, thank you!!

(Obviously, it worked)

"Gary''s Student" wrote:

If the sheet name is in A1 then:
=INDIRECT(A1 & "!" & "$L$52")

--
Gary''s Student - gsnu200767

You have managed to answer a question I didn't yet have ... thanks!

"Michael" wrote:

Use the ampersand to concatenate the values for Example
If you have selected column "C" to hold the worksheet name, and you still
want to show the value of 'P1'!$L$52 you would something like this:

='P1'!$L$52&$C1 and so on....

--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"ptrip" wrote:

I have a simple formula which reads:

='P1'!$L$52

Where 'P1' is a worksheet name.

I want the value 'P1' to be the contents of another cell.

Reason being:

This worksheet is a summary worksheet. Each row contains multiple values
(or formulas) from a different worksheet. I'd like to have one column where I
type the worksheet name and have that name automatically inserted into the
formulas throughout the row.

I apologize if this has been asked a hundred times before, I just don't know
a good search string to enter!

Thanks for your help.

The sheet names aren't necessarily in any sort of order, those I will hand
enter. I just wanted the cell I entered the names into to be referenced by
other formulas within that row (I know, confusing).

"Gary's Student" solution answered by question ... but thanks so much for
your time!

"Pete_UK" wrote:

I'm not sure which cell contains the worksheet name - assume it is P1,
and then use this formula:

=INDIRECT("'"&P1&"'!$L$52")

Copy this down, and it will take the sheet name from P1, then P2, P3
etc. I've assumed that the worksheets are all in the same workbook.

Hope this helps.

Pete

On Feb 4, 1:53 pm, ptrip wrote:
I have a simple formula which reads:

='P1'!$L$52

Where 'P1' is a worksheet name.

I want the value 'P1' to be the contents of another cell.

Reason being:

This worksheet is a summary worksheet. Each row contains multiple values
(or formulas) from a different worksheet. I'd like to have one column where I
type the worksheet name and have that name automatically inserted into the
formulas throughout the row.

I apologize if this has been asked a hundred times before, I just don't know
a good search string to enter!

Thanks for your help.

This gave me errors, unfortuantely. Gary's student had a formula that worked.

I appreciate your time though!

"Bernard Liengme" wrote:

=INDIRECT("'"&A1&"'!$L$52")
that is
( double-quote single-quote &A1 double-quote single-quote !$L$52
double-quote )

With the text P1 in cell A1
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ptrip" wrote in message
...
I have a simple formula which reads:

='P1'!$L$52

Where 'P1' is a worksheet name.

I want the value 'P1' to be the contents of another cell.

Reason being:

This worksheet is a summary worksheet. Each row contains multiple values
(or formulas) from a different worksheet. I'd like to have one column
where I
type the worksheet name and have that name automatically inserted into the
formulas throughout the row.

I apologize if this has been asked a hundred times before, I just don't
know
a good search string to enter!

Thanks for your help.

Suggest a slight refinement to Gary's:
=INDIRECT(A1 & "!" & "$L$52")

ie use it like this:
=INDIRECT("'"& A1 & "'!L52")

The concat of the single apostrophes before and after the sheetname will
make it work even if the sheetname were to contain spaces, eg: P 1, instead
of P1.

The other simplification is a minor one where we can drop the $ signs for
the L52 and just meld it with the ! since the cell ref here is just a
textstring, it won't change.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks! Most, but not necessarily all, of my panels have no spaces. But I'm
sure this alteration will save a couple of hairs in the future!

"Max" wrote:

Suggest a slight refinement to Gary's:
=INDIRECT(A1 & "!" & "$L$52")

ie use it like this:
=INDIRECT("'"& A1 & "'!L52")

The concat of the single apostrophes before and after the sheetname will
make it work even if the sheetname were to contain spaces, eg: P 1, instead
of P1.

The other simplification is a minor one where we can drop the $ signs for
the L52 and just meld it with the ! since the cell ref here is just a
textstring, it won't change.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Not confusing to me - the formula would work if you put your sheet
name in P1, and they can be in any order. If you use A1 to enter your
sheet name, then change it to:

=INDIRECT("'"&A1&"'!$L$52")

which is what you have been given elsewhere.

Pete

On Feb 4, 3:08*pm, ptrip wrote:
The sheet names aren't necessarily in any sort of order, those I will hand
enter. *I just wanted the cell I entered the names into to be referenced by
other formulas within that row (I know, confusing).

"Gary's Student" solution answered by question ... but thanks so much for
your time!



"Pete_UK" wrote:
I'm not sure which cell contains the worksheet name - assume it is P1,
and then use this formula:

=INDIRECT("'"&P1&"'!$L$52")

Copy this down, and it will take the sheet name from P1, then P2, P3
etc. I've assumed that the worksheets are all in the same workbook.

Hope this helps.

Pete

On Feb 4, 1:53 pm, ptrip wrote:
I have a simple formula which reads:

='P1'!$L$52

Where 'P1' is a worksheet name.

I want the value 'P1' to be the contents of another cell.

Reason being:

This worksheet is a summary worksheet. *Each row contains multiple values
(or formulas) from a different worksheet. I'd like to have one column where I
type the worksheet name and have that name automatically inserted into the
formulas throughout the row.

I apologize if this has been asked a hundred times before, I just don't know
a good search string to enter!

Thanks for your help.- Hide quoted text -

- Show quoted text -

Ah-ha ... now I see!

"Pete_UK" wrote:

Not confusing to me - the formula would work if you put your sheet
name in P1, and they can be in any order. If you use A1 to enter your
sheet name, then change it to:

=INDIRECT("'"&A1&"'!$L$52")

which is what you have been given elsewhere.

Pete

On Feb 4, 3:08 pm, ptrip wrote:
The sheet names aren't necessarily in any sort of order, those I will hand
enter. I just wanted the cell I entered the names into to be referenced by
other formulas within that row (I know, confusing).

"Gary's Student" solution answered by question ... but thanks so much for
your time!



"Pete_UK" wrote:
I'm not sure which cell contains the worksheet name - assume it is P1,
and then use this formula:

=INDIRECT("'"&P1&"'!$L$52")

Copy this down, and it will take the sheet name from P1, then P2, P3
etc. I've assumed that the worksheets are all in the same workbook.

Hope this helps.

Pete

On Feb 4, 1:53 pm, ptrip wrote:
I have a simple formula which reads:

='P1'!$L$52

Where 'P1' is a worksheet name.

I want the value 'P1' to be the contents of another cell.

Reason being:

This worksheet is a summary worksheet. Each row contains multiple values
(or formulas) from a different worksheet. I'd like to have one column where I
type the worksheet name and have that name automatically inserted into the
formulas throughout the row.

I apologize if this has been asked a hundred times before, I just don't know
a good search string to enter!

Thanks for your help.- Hide quoted text -

- Show quoted text -

Good !!

Pete

On Feb 4, 4:05*pm, ptrip wrote:
Ah-ha ... now I see!

"ptrip" wrote:
Thanks! Most, but not necessarily all, of my panels have no spaces. But I'm
sure this alteration will save a couple of hairs in the future!

Welcome. I'd use the suggested version:
=INDIRECT("'"& A1 & "'!L52")
all of the time, to pre-empt all possibilities in the sheetnames
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---