If you were trying to debug your workbook, you may want to get a copy of Jan
Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp



"Adam Kroger

Dave:
Thank you, works like a dream. Fixed a problem that had been eluding me for
a while.

Adam

"Dave Peterson" wrote in message
...
This will return the first named range that it finds. A cell can be in
lots of
different named ranges.

Option Explicit
Function myName(rng As Range) As Variant

Application.Volatile True

Dim nm As Name
Dim testRng As Range

Set rng = rng.Cells(1)

myName = CVErr(xlErrRef)
For Each nm In Application.Caller.Parent.Parent.Names
Set testRng = Nothing
On Error Resume Next
Set testRng = nm.RefersToRange
On Error GoTo 0

If testRng Is Nothing Then
'do nothing
Else
If rng.Parent.Parent.Name = testRng.Parent.Parent.Name Then
If rng.Parent.Name = testRng.Parent.Name Then
If Intersect(rng, testRng) Is Nothing Then
'keep looking
Else
myName = nm.Name
Exit For
End If
End If
End If
End If
Next nm

End Function

=myName(a1)
in any cell (including A1).

"Adam Kroger

Is there a way to return, via a function, the name of the range a cell
resides in
example:
range = A1:C10

I want a formula that, when put in cell B5, will return "range"

ans extra bonus would be one that can return the name of the range that
another cell resides in.
Example
Range2 - A12:C21

B2 is =whatever_formula(A15) -- Range2

thanks

--

Dave Peterson

--

Dave Peterson