Is there an excel function that will return the name of the named range in cell A1

[PeterMac65]

Hi,

If I gave cell A1 the range-name "Apple", is there a function that will return "Apple"?



eg =example(A1) returns "Apple



I would prefer not to use VBA, but will if there is no other option.



Regards Peter

 

[bebo021999]

Like this:

Function GetNamedRange(cell As Range) As String
    Dim nm As Name
    For Each nm In ThisWorkbook.Names
        If Not Intersect(cell, Range(nm.RefersTo)) Is Nothing Then
            GetNamedRange = nm.Name
            Exit Function
        End If
    Next nm
    GetNamedRange = ""
End Function

 

[Cubist]

You can get the entire list by Formula Tab -> Use in Range -> Paste Name->Paste List.

 

[PeterMac65]

Like this:

Function GetNamedRange(cell As Range) As String
    Dim nm As Name
    For Each nm In ThisWorkbook.Names
        If Not Intersect(cell, Range(nm.RefersTo)) Is Nothing Then
            GetNamedRange = nm.Name
            Exit Function
        End If
    Next nm
    GetNamedRange = ""
End Function

Thanks Bebo,
I have several hundred rangenames and will be calling this function many times. Is there a way to restrict the number of names to loop through? Perhaps just the cell passed into the function?

Or Is there a non VBA alternative?

If not I might just have to type the named ranges into about 50 formulas.

 

[Fluff]

How about

Function GetName(Rng As Range) As String
   GetName = Rng.Name.Name
End Function

If the named range is more than a single cell, you need to pass the entire range.

 

[PeterMac65]

Function GetName(Rng As Range) As String GetName = Rng.Name.Name End Function

Thanks Fluff. That is perfect.
I use it as follows"
=XLOOKUP(getname(iInsulationToSlab),specRangeName,specFormula,"Not found")

 

[Fluff]

Glad we could help & thanks for the feedback.