Is there a function that will return the name of a cell not the contents? I
want to look up a team name in a list and get the named range (i.e. TEAM5)

Thanks
--
Jim T

I don't think you can do this with any of Excel's built in functions. With
VBA.
--
Cheers,
Shane Devenshire


"Jim Tibbetts" wrote:

Is there a function that will return the name of a cell not the contents? I
want to look up a team name in a list and get the named range (i.e. TEAM5)

Thanks
--
Jim T

I didn't think so. Thanks for the comeback.
--
Jim T


"ShaneDevenshire" wrote:

I don't think you can do this with any of Excel's built in functions. With
VBA.
--
Cheers,
Shane Devenshire


"Jim Tibbetts" wrote:

Is there a function that will return the name of a cell not the contents? I
want to look up a team name in a list and get the named range (i.e. TEAM5)

Thanks
--
Jim T

Jim, I took a look around and found this solution originally posted by
Bob Phillips and Zack Barresse back in 2005:

Option Explicit
Option Compare Text
Public Function NAMEDRANGE(celRef) As String
Dim iName As Name, wbCall As Workbook
If TypeOf celRef Is Range Then
celRef = celRef.Address
End If
If Not celRef Like "*!$" Then
celRef = Application.Caller.Parent.Name & "!" & celRef
End If
Set wbCall = Workbooks(Application.Caller.Parent.Parent.Name)
For Each iName In wbCall.Names
If Not Intersect(Range(celRef), Range(iName.RefersTo)) Is Nothing
Then
NAMEDRANGE = iName.Name
Set wbCall = Nothing
Exit Function
End If
Next
NAMEDRANGE = "Not Found"
Set wbCall = Nothing
End Function

Called like ..
=NAMEDRANGE("Sheet1!$A$1")

See: http://www.pcreview.co.uk/forums/thread-2090701.php for the
original post.

Hope this helps,

David

Jim Tibbetts wrote:

Is there a function that will return the name of a cell not the contents? I
want to look up a team name in a list and get the named range (i.e. TEAM5)

Thanks
--
Jim T