convert text input to cell reference

[kimfungloy]

Hi everyone,

for a project, I have to insert names of cells into excel. these cells, specified by me, should then be used for further calculations. So for example in cell A1, I have the tekst J4. I would like it to show on cell B1 as $J$4, so that I can use the real cell J4 in further calculations. What functions should I use?

Capture.JPG

Thank you

[Jonmo1]

You can use the INDIRECT function..

=INDIRECT(A1)

That will refer to the actual cell specified in A1

[kimfungloy]

Indirect returns the value of the cell and not the address of the cell.

So if I do Indirect(A2), and A2 contains text "J4" then indirect returns the value of cell J4 and not the cell address $J$4

[Jonmo1]

You said that's your ultimate goal.

so that I can use the real cell J4 in further calculations.

My suggestion bypasses the unnecessary step of converting J4 to $J$4, and just uses the real cell J4.

[FlameRetired]

If I am interpreting correctly you wish to build those addresses in column B referencing what is in column A? Try this in column B.

Please Login or Register  to view this content.

Edit:
That only works if referencing cell address with one letter. Otherwise use this.

Please Login or Register  to view this content.

Another edit:

Build your formula in column C …… just as an example “=B2&"+"&5”.

This will not evaluate by itself. In this case you will need to use an old Excel 4.0 macro function called EVALUATE. You cannot use this in the spreadsheet. You must use Name Manager to build a formula.

With D2 selected go to Name Manager and build this formula. I called mine “eval”. In the refers to box type this

Please Login or Register  to view this content.

Then in D2 type =eval and hit Enter.
There are some down sides. Excel will prompt you to save as a Macro-enabled file. Also, whenever you change values in J4, K4, P5 or S2 you will need to recalculate the workbook. To do this ……. while pressing and holding CTRL + ALT hit the F9 function key.

File is attached.

[Jonmo1]

And I get the same result using just
=INDIRECT(A2)
Bypassing all the extra steps.

I don't understand the purpose of converting J4 to $J$4

The $'s are completely unnecessary for this task.

Indirect.xlsm

[FlameRetired]

And I get the same result using just
=INDIRECT(A2)
Bypassing all the extra steps.

I don't understand the purpose of converting J4 to $J$4

The $'s are completely unnecessary for this task.

Attachment 385705

Me neither....unless I've completely missed the point.

[Jonmo1]

Me neither....unless I've completely missed the point.

Pretty sure we're not getting the whole story

these cells, specified by me, should then be used for further calculations.

It would help if the OP could elaborate on what he/she is planning on actually doing with these references once they're created.

You can use indirect in larger formulas like
=SUM(INDIRECT(A1):INDIRECT(B1))

If A1 contains the text string J4 and B1 contains text string K5
Then this would be the equivelent of
=SUM(J4:K5)

[FlameRetired]

Pretty sure we're not getting the whole story


It would help if the OP could elaborate on what he/she is planning on actually doing with these references once they're created.

Yes.....sounds like there is more than one request here.