How to show the defined name of another cell

[Guest]

Is it possible to show the defined name (from insert/name/define) of another
cell? For instance: cell B1 is defined as "abc". What do i type in cell A1 to
get this texts text ("abc") from cell B1?

Please help!

 

[Bernard Liengme]

This is as far as I can get today.
If A11 is named ABC then =MYNAME("$A$11") will return ABC

Function myname(rng)
Set nms = ActiveWorkbook.Names
myname = "BLANK"
For r = 1 To nms.Count
If rng = nms(r).RefersToRange.Address Then
myname = nms(r).Name
End If
Next
End Function

 

[Guest]

Hi, thanks for the respond.

I couldn't get that udf to work properly. I just get "blank" in the cell
containing the myname-formula event though the refered cell has a defined
name.

Please help!

Bernard Liengme skrev:

 

[Guest]

I just realised your udf works, but only if you add the "" and use the
absolute adress ($<letter>$<number>), as you pointed out with:
=MYNAME("$A$11") . Sorry for my inattention.

The problem is that the udf should be edited so that the quotation "" and
absolute references doesn't need to be included. This because these premises
make the MYNAME(<address>) not work with Excel's AutoFill function. And the
AutoFill is very important for me to be able to use.