show cell reference as text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BEEJAY - 27 May 2008 18:36 GMT

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.

Reply to this Message

Joel - 27 May 2008 18:48 GMT

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

> Greetings:
> How to show a cell reference as Text:
[quoted text clipped - 19 lines]
> but I can't seem to get a handle on it.
> Help, please.

Reply to this Message

Rick Rothstein (MVP - VB) - 27 May 2008 19:05 GMT

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

> There is a problem with th original code. It will only return a value if
> the
[quoted text clipped - 31 lines]
>> but I can't seem to get a handle on it.
>> Help, please.

Reply to this Message

BEEJAY - 27 May 2008 19:16 GMT

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?

> There is a problem with th original code. It will only return a value if the
> reference is on another worksheet. try this change
[quoted text clipped - 30 lines]
> > but I can't seem to get a handle on it.
> > Help, please.

Reply to this Message

BEEJAY - 27 May 2008 19:41 GMT

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?

> Joel:
> Thanks for the prompt response.
[quoted text clipped - 36 lines]
> > > but I can't seem to get a handle on it.
> > > Help, please.

Reply to this Message

Rick Rothstein (MVP - VB) - 27 May 2008 19:53 GMT

> 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

Reply to this Message