Using cell content as reference in formula

P

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

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
 
M

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

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
 
P

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!
 
P

ptrip

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

I appreciate your time though!
 
M

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

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!
 
P

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
 
P

ptrip

Ah-ha ... now I see!

Pete_UK said:
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
 
M

Max

ptrip said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top