Formula return cell defined name

Solution 1:

I came up with a quick workaround, though admittedly, a little limited.

1. do a Paste List of all defined names in a clear area of a sheet.
2. The following formula will create a string resembling the fully qualified cell address you're looking for, matching it in the list, and returning the cell name from the adjacent column =INDEX(NameList,MATCH("='"&SUBSTITUTE(CELL("filename"),"fully_qualified_filename","")&"'!"&CELL("address"),NameReferences,0))

It's obviously limited to names pointing to a single cell, but it could be extended to search cell ranges.

It works for me because the name of the cell displaying the data is the name of the data field, so no matter where I put it, the reference will always be correct.

While I'm working on the project I included a statement in an often-run macro that refreshes the Names list to keep it current.

Solution 2:

I cannot take credit for writing this code, but it does precisely what you need. I needed the same feature some years ago, and stumbled upon this code somewhere in cyberspace, perhaps modified it a bit, but I can't remember either way. It's worked great for me.

It works like this:

1. Paste the code below into a VBA module of an Excel macro-enabled workbook (*.xlsm).
2. Then, type into an Excel worksheet cell something like "=CellName(F19)" and it will return something like "rngSomeNamedCell".

Here is the code:

Public Function CellName(cel As Range) As Variant
Dim nm As Name
    For Each nm In Names
        If nm.RefersTo = "=" & cel.Parent.Name & "!" & cel.Address Then
            CellName = nm.Name
            Exit Function
        End If
    Next
CellName = CVErr(xlErrNA)
End Function