Is there a way or formula to display the name of the range a cell is in?

[mark hansen]

I have a worksheet with 225 cells that have a range name. Each range is a single cell. Is there a way to have the Range name display in the cell?

I've done some searches and discovered I can get a list of all of my ranges and the cell reference and I can paste them in worksheet as a reference.
I know I can use name manager to display a list of range name and where it is....
I know I can zoom out to 40% to get range names, doesn't work when when the range name refers to only one cell.

What I (think) I want to do is put a formula in a cell that will display the range name the cell is contained in. In my case they are one cell ranges that VBA refers to.

Am I barking up a dead tree?

Thanks
Mark

 

[sykes]

I put this in a module, then selected the whole range of cells (225, in your case), then ran the code.
as always, test on a copy, first...

Sub show_names()
Dim cl As Range

    For Each cl In Selection
        On Error Resume Next
        cl.Value = cl.Name.Name
    Next
End Sub

The error handler's there, in case it finds cells with no range names.

 

[JGordon11]

or as a user defined function (UDF):

Function RangeName() As String
    On Error Resume Next
    RangeName = Application.Caller.Name.Name
End Function

In this example A1 is named TestRange and contains the UDF, A2, A3, and A4 call the UDF also but have no names, and B1 contains a formula to return the address of TestRange:

Book1
	A	B
1	TestRange	$A$1
2
3
4
Sheet1

Cell Formulas
Range	Formula
B1	B1	=CELL("address",TestRange)
A1:A4	A1	=RangeName()

Named Ranges
Name	Refers To	Cells
TestRange	=Sheet1!$A$1	B1

 

[mark hansen]

Thanks for the quick replies . Sykes, this is great, it identified all the single cell range names and where they were. (Also pointed out I had few older range names I overlooked in Name Manager and needed to delete).

Gordon, thanks for your help too.

 

[sykes]

Pleasure, and thanks for the feedback.
If you mark it as the solution, it may help others more quickly find help with similar requirements. Ta.

 

[JenniferMurphy]

I found this thread while looking for a way to display named ranges on the sheet. I combined the code from sykes and JGordon11 to make this UDF. If anyone can see a way to make it better, please let me know.

'======================================================
' Return the name of the cell(s) at pAddr, if any.
' It will not return the name of a range of more
' than 1 cell unless the entire range is selected
'======================================================
Function RangeName(pAddr As Range) As String

On Error GoTo NoName        'Error = no name
RangeName = pAddr.Name.Name 'Get the anme, if any
                            'Strip off sheet name
RangeName = Mid(RangeName, InStr(1, RangeName, "!") + 1)
GoTo Done

NoName:
RangeName = "No name"

Done:

End Function

I tested it here. Let me know if I need to add tests.

Range Names, Displaying.xlsx
	C	D	E
4			Comments
5	50.0%	Probability of winning 1 game (P)	Can be used in expressions
6	25	Number of games left to play (GamesLeft)
7		No name	Unnamed cell
8	1	No name	Will not return the name if entire range is not passed
9	2	GameNum	But it will if it is
10	3	P	Can be called from anywhere
11		P	Can use relative addressing
12		P	Or absolute addressing
Sheet1

Cell Formulas
Range	Formula
D5	D5	="Probability of winning 1 game (" & rangename(P) & ")"
D6	D6	="Number of games left to play (" & rangename(GamesLeft) & ")"
D7:D8	D7	=rangename(C7)
D9	D9	=rangename(GameNum)
D10	D10	=rangename(P)
D11	D11	=rangename(C5)
D12	D12	=rangename($C$5)

Named Ranges
Name	Refers To	Cells
GameNum	=Sheet1!$C$8:$C$10	D8:D9
GamesLeft	=Sheet1!$C$6	D6
P	=Sheet1!$C$5	D5, D10:D12

 

[JGordon11]

I'm not seeing instances when pAddr.Name.Name returns the name prefixed with the sheet name. In my tests it always returns just the range name. So I would write it as:

Function RangeName(pAddr As Range) As String
    On Error Resume Next
    RangeName = pAddr.Name.Name
    If RangeName = "" Then RangeName = "No name"
End Function

or if there are cases when the sheet name comes attached:

Function RangeName(pAddr As Range) As String
    On Error Resume Next
    With pAddr.Name: RangeName = Mid(.Name, InStr(1, .Name, "!") + 1): End With
    If RangeName = "" Then RangeName = "No name"
End Function

 

[sykes]

... also, if you DID wish to retain your original code, I don't think you need the GoTo ...

'======================================================
' Return the name of the cell(s) at pAddr, if any.
' It will not return the name of a range of more
' than 1 cell unless the entire range is selected
'======================================================
Function RangeName(pAddr As Range) As String

On Error GoTo NoName        'Error = no name
RangeName = pAddr.Name.Name 'Get the anme, if any
                            'Strip off sheet name
RangeName = Mid(RangeName, InStr(1, RangeName, "!") + 1)
Exit Sub

NoName:
RangeName = "No name"

End Function

 

[JenniferMurphy]

I'm not seeing instances when pAddr.Name.Name returns the name prefixed with the sheet name. In my tests it always returns just the range name. So I would write it as:

I am, at least right now. I think this code will handle both cases.

Function RangeName(pAddr As Range) As String

Dim i As Long                 'Index of "!" or zero

RangeName = "No name"         'Set default
On Error GoTo NoName          'Error = no name
RangeName = pAddr.Name.Name   'Get the name
i = InStr(1, RangeName, "!")  'Get index of "!", if any
If i > 0 Then                 'If there is a sheetname, strip it off
  RangeName = Mid(RangeName, InStr(1, RangeName, "!") + 1)
End If

NoName:
End Function

 

[JenniferMurphy]

... also, if you DID wish to retain your original code, I don't think you need the GoTo ...

I think you meant the second GoTo, right?

My latest code posted above does that.