show cell reference as text
 

Greetings:
How to show a cell reference as Text:
Example:
Cell C3: Has input of =+G12, Shows value of 236

Cell D3: Need to "extract" the cell reference used in C3.
To show as text. Should read G12

As per instructions found in ng (if I'm reading it correctly):
In cell D3, I entered =ws(C3)
I then copy and paste the code in a new module, in the workbook.

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
Back to my spread-sheet, Cell D3 reads =ws(C3).
It does NOT read G12, as I expected.

I have a feeling I'm overlooking something VERY obvious here,
but I can't seem to get a handle on it.
Help, please.

show cell reference as text
 

There is a problem with th original code. It will only return a value if the
reference is on another worksheet. try this change

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

"BEEJAY" wrote:

Greetings:
How to show a cell reference as Text:
Example:
Cell C3: Has input of =+G12, Shows value of 236

Cell D3: Need to "extract" the cell reference used in C3.
To show as text. Should read G12

As per instructions found in ng (if I'm reading it correctly):
In cell D3, I entered =ws(C3)
I then copy and paste the code in a new module, in the workbook.

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
Back to my spread-sheet, Cell D3 reads =ws(C3).
It does NOT read G12, as I expected.

I have a feeling I'm overlooking something VERY obvious here,
but I can't seem to get a handle on it.
Help, please.

show cell reference as text
 

This function seems to work also...

Function ws(rg As Range) As String
ws = Replace(Replace(Mid(rg.Formula, 1 + _
InStr(1, rg.Formula, "!")), "=", ""), "+", "")
End Function

Rick


"Joel" wrote in message
...
There is a problem with th original code. It will only return a value if
the
reference is on another worksheet. try this change

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

"BEEJAY" wrote:

Greetings:
How to show a cell reference as Text:
Example:
Cell C3: Has input of =+G12, Shows value of 236

Cell D3: Need to "extract" the cell reference used in C3.
To show as text. Should read G12

As per instructions found in ng (if I'm reading it correctly):
In cell D3, I entered =ws(C3)
I then copy and paste the code in a new module, in the workbook.

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
Back to my spread-sheet, Cell D3 reads =ws(C3).
It does NOT read G12, as I expected.

I have a feeling I'm overlooking something VERY obvious here,
but I can't seem to get a handle on it.
Help, please.

show cell reference as text
 

Joel:
Thanks for the prompt response.
However, I still can't get it to work.
As indicated before, Cell D3 still shows =ws(C3) and not G12, as expected
Any other ideas?

"Joel" wrote:

There is a problem with th original code. It will only return a value if the
reference is on another worksheet. try this change

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

"BEEJAY" wrote:

Greetings:
How to show a cell reference as Text:
Example:
Cell C3: Has input of =+G12, Shows value of 236

Cell D3: Need to "extract" the cell reference used in C3.
To show as text. Should read G12

As per instructions found in ng (if I'm reading it correctly):
In cell D3, I entered =ws(C3)
I then copy and paste the code in a new module, in the workbook.

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
Back to my spread-sheet, Cell D3 reads =ws(C3).
It does NOT read G12, as I expected.

I have a feeling I'm overlooking something VERY obvious here,
but I can't seem to get a handle on it.
Help, please.

show cell reference as text
 

Joel:
I don't know what I did, but just "bouncing" off and on Cell D3, and all of
a sudden it worked.
Rick R:
For education purposes I will yet work with your submission, as well.
Thank-you

Now for the next step. I hope you can help with this, as well.
In cell C13, I have entered: =OFFSET(INDIRECT,("D3"),,1).
It returns #NAME?. I presume it is somehow NOT picking up the cell
reference, out of Cell D3, namely G12. Do you have any magic to work on this
one?




"BEEJAY" wrote:

Joel:
Thanks for the prompt response.
However, I still can't get it to work.
As indicated before, Cell D3 still shows =ws(C3) and not G12, as expected
Any other ideas?

"Joel" wrote:

There is a problem with th original code. It will only return a value if the
reference is on another worksheet. try this change

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

"BEEJAY" wrote:

Greetings:
How to show a cell reference as Text:
Example:
Cell C3: Has input of =+G12, Shows value of 236

Cell D3: Need to "extract" the cell reference used in C3.
To show as text. Should read G12

As per instructions found in ng (if I'm reading it correctly):
In cell D3, I entered =ws(C3)
I then copy and paste the code in a new module, in the workbook.

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
Back to my spread-sheet, Cell D3 reads =ws(C3).
It does NOT read G12, as I expected.

I have a feeling I'm overlooking something VERY obvious here,
but I can't seem to get a handle on it.
Help, please.

show cell reference as text
 

Rick R:
For education purposes I will yet work with your submission, as well.

If you have any questions about the code, feel free to ask.

Rick