Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show cell reference as text | Excel Discussion (Misc queries) | |||
Show Cell Reference | Excel Worksheet Functions | |||
Show values in formula instead of cell reference | Excel Worksheet Functions | |||
Formula to show zero if reference cell is zero? tricky! | Excel Worksheet Functions | |||
help to show formart in reference cell | Excel Worksheet Functions |