Resolve a text cell reference to the cell's value
 

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?

Resolve a text cell reference to the cell's value
 

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.
--
Gary''s Student - gsnu200909


"BCDS" wrote:

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?

Resolve a text cell reference to the cell's value
 

You could use:
=indirect(d27)*e27

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



BCDS wrote:

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?

--

Dave Peterson

Resolve a text cell reference to the cell's value
 

THANK YOU!

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200909


"BCDS" wrote:

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?