Worksheet function to return name of range?

[JenniferMurphy]

Is there a way in a worksheet (without using VBA) to obtain the name of a range, if one is defined?

For example, suppose I have assigned the name "HeaderRow" to $7:$7. Is there a way for me to obtain the text "HeaderRow" if I provide the row number (7)?

The Address function doesn't seem to do it and there doesn't seem to be a Name() function.

Thanks

 

[shg]

Assuming this is for development and not deployment, why not just put a UDF in Personal to do this?

	A​	B​	C​
1​	myCol	(Workbook) Sheet1!myCol Refers To: =Sheet1!B:B	B1: =sGetname(A1)
2​	myRow	(Workbook) Sheet1!myRow Refers To: =Sheet1!2:2
3​	relN	(Workbook) Sheet1!relN Refers To: =Sheet1!B2
4​	Bob	Sheet4!Bob Refers To: =Sheet4!$A$1

 

[shg]

Or this:

	A​	B​	C​
1​		Sheet4!Jennifer	B1: =WhatsherName(A1)

 

[Greg Truby]

The main problem is that if I change a name, the cell containing the name does not change.

Ah. Got it. You were actually on the right track. So... create a named formula (just like naming a range) called FormulaLeft

In the RefersTo box of the name definition dialog put

=GET.CELL(6,OFFSET(INDIRECT("RC",FALSE),0,-1))

Now, in A1, put a formula that references the name you're interested in with just the equals sign. Example:

=BillJelenIsASpaceCowboy

Then in B1 put the following formula:

=RIGHT(FormulaLeft,LEN(FormulaLeft)-1)

Then in C1 you can put:

=ADDRESS(ROW(INDIRECT(B1)),COLUMN(INDIRECT(B1)),1,1,MID(CELL("filename",B1),FIND("]",CELL("filename",B1))+1,255))

And C1 should tell you the address of whereever BillJelenIsASpaceCowboy points to.

Go into the Name Manager and change BillJelenIsASpaceCowboy's name to be BillJelenIsASteelyEyedMissileMan

A1's formula will update to the new name which B1 will reflect and C1 keeps working using the new name.

 

[Rick Rothstein]

It just occurred to me that there might be another even more arcane work-around. I have a GetFormula UDF that I wrote some time ago that will return the formula in a cell.

But you said "without using VBA" in Message #1 ... if that has changed, then you can use this UDF to get the Defined Name for the range wholly containing within, or exactly equal to, an actual range that has a Defined Name. So, if you have a Defined Name covering, say, the range G5:H10, then specifying any single cell, or multiple range of cells, that are wholly contained within the range G5:H10 (so, for example, you could specify G8, or G6:H9 or G5:H10 as the argument to my UDF, and it would return the Defined Name containing those cells. If you change the defined name in the Name Manager, then the formula containing my UDF will change accordingly. Would such a UDF be helpful to you? If so, here is the code for it...

[table="width: 500"]
[tr]
	[td]Function GetName(Rng As Range) As String
  Dim Nm As Name
  Application.Volatile
  For Each Nm In Names
    If Not Intersect(Rng, Nm.RefersToRange) Is Nothing Then
      If Union(Rng, Nm.RefersToRange).Address = Nm.RefersToRange.Address Then
        GetName = Nm.Name
        Exit For
      End If
    End If
  Next
End Function[/td]
[/tr]
[/table]

 

[JenniferMurphy]

Assuming this is for development and not deployment, why not just put a UDF in Personal to do this?

	A​	B​	C​
1​	myCol	(Workbook) Sheet1!myCol Refers To: =Sheet1!B:B	B1: =sGetname(A1)
2​	myRow	(Workbook) Sheet1!myRow Refers To: =Sheet1!2:2
3​	relN	(Workbook) Sheet1!relN Refers To: =Sheet1!B2
4​	Bob	Sheet4!Bob Refers To: =Sheet4!$A$1

<tbody>
</tbody>

This is for personal use only, so it doesn't need to pass any international code standards.

I guess I'm too dense. I don't understand what to put in the UDF. Is there a VBA function that will return the name given the range? Maybe something like:

Dim sName as String
sName = RangeName("$2:$2")

I tried these:

?range("$2:$2").Address
$2:$2
?range("$2:$2").ApplyNames
?range("$2:$2").ID

?range("$2:$2").ListNames
True
?range("$2:$2").name
='1 loss'!$2:$2
?range("$2:$2").Range

Some got errors. None returned "HeaderRow".

Here's a simplified version of my sheet:

R/C	C	D	E	F	G	H	I
4	HdrRow	0	1	2	3	4	Sum
5	NumEvents	3	5	12	7	11	38
6	Percent	8%	13%	32%	18%	29%	100%

<tbody>
</tbody>

The labels in Col C are the names assigned to those rows. For example, "$4:$4" is assigned the name "HdrRow".

I would like to replace the literal text in Col C with a formula or function that will return the exact same result.

 

[JenniferMurphy]

But you said "without using VBA" in Message #1 ...

I should have said, "preferably without using VBA". I was hoping for a simple worksheet function. I seem to be writing UDFs for everything these days and many of them should, IMNSHO, be built into Excel.

if that has changed, then you can use this UDF to get the Defined Name for the range wholly containing within, or exactly equal to, an actual range that has a Defined Name. So, if you have a Defined Name covering, say, the range G5:H10, then specifying any single cell, or multiple range of cells, that are wholly contained within the range G5:H10 (so, for example, you could specify G8, or G6:H9 or G5:H10 as the argument to my UDF, and it would return the Defined Name containing those cells. If you change the defined name in the Name Manager, then the formula containing my UDF will change accordingly. Would such a UDF be helpful to you? If so, here is the code for it...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function GetName(Rng As Range) As String
  Dim Nm As Name
  Application.Volatile
  For Each Nm In Names
    If Not Intersect(Rng, Nm.RefersToRange) Is Nothing Then
      If Union(Rng, Nm.RefersToRange).Address = Nm.RefersToRange.Address Then
        GetName = Nm.Name
        Exit For
      End If
    End If
  Next
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Let me play with that a bit. Is there a way to get it to only return a result if the passed range matches the name exactly?

Thanks

 

[Rick Rothstein]

Is there a way to get it to only return a result if the passed range matches the name exactly?

Sure...

Function GetName(Rng As Range) As String
  Dim Nm As Name
  Application.Volatile
  For Each Nm In Names
    If Rng.Address = Nm.RefersToRange.Address Then
      GetName = Nm.Name
      Exit For
    End If
  Next
End Function

 

[Greg Truby]

...(without using VBA)...

...The main problem is that if I change a name, the cell containing the name does not change....

I should have said, "preferably without using VBA".

Were you able to try out the non-VBA-based solution I provided in post #13 on this thread? As far as I understand your requirements, that ought to provide the information you seek.

Also, the use of OFFSET() in the definition of FormulaLeft was overkill. FormulaLeft can be simplified to simply be defined as:

=GET.CELL(6,INDIRECT("RC[-1]",FALSE))