Resolve a text cell reference to the cell's value

[BCDS]

I have 2 cells on a work sheet that I concatenate together in cell D27 to
give the string '#1'!P15 which refers to cell P15 on worksheet #1

D27="'"&D$24&"'!"&C27

I want to do this

=D27*E27

But I get the #VALUE! result

How do I get D27 to have the value of cell P15 on worksheet #1 instead of a
text string?

 

[Gary''s Student]

Use the INDIRECT() function.

If A1 contains:
Sheet1
and A2 contains:
P15

Then the formula
=A1 & "!" & A2
will naturally display:
Sheet1!P15


But:
=INDIRECT(A1 & "!" & A2)
will display the actual contents of cell P15 on Sheet1.

 

[Dave Peterson]

You could use:
=indirect(d27)*e27

Or you could put the =indirect() function in your cell in D27:
=indirect("'"&D$24&"'!"&C27)

 

[BCDS]

THANK YOU!

Use the INDIRECT() function.

If A1 contains:
Sheet1
and A2 contains:
P15

Then the formula
=A1 & "!" & A2
will naturally display:
Sheet1!P15


But:
=INDIRECT(A1 & "!" & A2)
will display the actual contents of cell P15 on Sheet1.