| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using 1 cell's value as a reference | Excel Discussion (Misc queries) | |||
| How do I Reference another cell's content including its format | Excel Worksheet Functions | |||
| no functions resolve when format of cells is text | Excel Worksheet Functions | |||
| using a cell's reference to return a value. | Excel Worksheet Functions | |||
| Changing named range reference depending on a cell's content | Excel Discussion (Misc queries) | |||
Linear Mode
