Show cell reference as text

[Guest]

How can I show a cell reference as a text? For example, if cell A1 contains
the formula =worksheet1!A1, it will return the contents of the cell
referenced. In a different cell, I'd like to show the text "=worksheet1!A1".
More specifically, I'd like to be able to extract the text "worksheet1" from
the original formula.

 

[PCLIVE]

It sounds like you want to do an indirect.
Let's say that cell D1 contains the text "A1" which could change to another
cell reference. But maybe you still want to reference worksheet1.

=INDIRECT("worksheet1!" & D1)

if you had "worksheet1!A1" in cell D1, then:
=INDIRECT(D1)

Does that help?
Paul

 

[Guest]

Thanks Paul. I'm trying to get a text version of the formula contained in
another cell.

INDIRECT returns a value for a given text reference...the opposite of my
problem.

 

[PCLIVE]

I don't know how to tell you to get the text other than to retype it and
precede your formula with an apostrophe,

example.
'=worksheet1!A1

or as a formula:
="=worksheet1!A1"

Regards,
Paul

--

 

[Ron Rosenfeld]

How can I show a cell reference as a text? For example, if cell A1 contains
the formula =worksheet1!A1, it will return the contents of the cell
referenced. In a different cell, I'd like to show the text "=worksheet1!A1".
More specifically, I'd like to be able to extract the text "worksheet1" from
the original formula.

You can do it with a UDF.

<alt-F11> opens the VB Editor

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this, enter =ws(cell_ref) in some cell.

=======================
Function ws(rg As Range) As String
If InStr(1, rg.Formula, "!") > 0 Then
ws = Mid(rg.Formula, 2, InStr(1, rg.Formula, "!") - 2)
End If
End Function
=========================
--ron

 

[Guest]

Thank you, Ron. This is exactly what I was looking for.

 

[Ron Rosenfeld]

Thank you, Ron. This is exactly what I was looking for.

You're welcome. Thanks for the feedback.
--ron