Using cell content as reference in formula

[ptrip]

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.

 

[Pete_UK]

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

 

[Gary''s Student]

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

 

[Michael]

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

 

[Bernard Liengme]

=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

 

[ptrip]

Thank you, thank you, thank you!!

(Obviously, it worked)

 

[ptrip]

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

 

[ptrip]

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!

 

[ptrip]

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

I appreciate your time though!

 

[Max]

Suggest a slight refinement to Gary's:
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.

---

 

[ptrip]

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!

 

[Pete_UK]

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

 

[ptrip]

Ah-ha ... now I see!

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

 

[Pete_UK]

Good !!

Pete

 

[Max]

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

---