Show cell reference as text
 

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.

Show cell reference as text
 

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





--

"tjc" wrote in message
...
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.

Show cell reference as text
 

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" wrote:

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





--

"tjc" wrote in message
...
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.

Show cell reference as text
 

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

--

"tjc" wrote in message
...
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" wrote:

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





--

"tjc" wrote in message
...
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.

Show cell reference as text
 

On Tue, 21 Aug 2007 11:36:01 -0700, tjc wrote:

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

Show cell reference as text
 

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

"Ron Rosenfeld" wrote:

On Tue, 21 Aug 2007 11:36:01 -0700, tjc wrote:

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

Show cell reference as text
 

On Fri, 31 Aug 2007 07:36:05 -0700, tjc wrote:

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

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