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

 

[Beyond_avarice]

Use the "Name Box" to the left of the formula bar.

 

[JenniferMurphy]

Use the "Name Box" to the left of the formula bar.

I've got to learn to be more specific. I want to use the name in a formula. I want a formula something like this:

=name(row(xyz))

Where xyz is a cell in the row in question. The result would be a text string that is the name of the row.

 

[XOR LX]

Can I ask why you want to return the Name? Is it with a view to then using that Name within some further formula construction? If so, there might be a much simpler way without obtaining the Name explicitly.

Regards

 

[JenniferMurphy]

Can I ask why you want to return the Name? Is it with a view to then using that Name within some further formula construction? If so, there might be a much simpler way without obtaining the Name explicitly.

Regards

I have a table within a sheet with a number of named rows. The rows are named so that I can use the names in formulas within that table. I'd like to display the row named in a column to the side of the table for easy reference. I have changed the names once or twice and forgotten to change the formulas, so this would be a check on that.

 

[XOR LX]

I have changed the names once or twice and forgotten to change the formulas, so this would be a check on that.

Not sure I understand. Whenever you rename a Defined Name, Excel automatically updates any formulas which reference that Defined Name accordingly.

Regards

 

[Beyond_avarice]

I don't believe there is a built in function that will do this. I'd be interested to see if someone knows how to do this.

 

[JenniferMurphy]

Not sure I understand. Whenever you rename a Defined Name, Excel automatically updates any formulas which reference that Defined Name accordingly.

Regards

But what if I change the formula or create a new row and mis-remember the correct row name? And before you tell me that I would then get a name error, I can tell you that I might get two names confused and choose the wrong one.

Now, do you know how to do what I want to do or do I have to jump through more hoops to convince you that it's a legitimate question? Sheesh!

 

[Greg Truby]

AFAIK, you can't do what you want to do in the direction you want to do it without using a UDF (VBA). However, having said that, you can certainly solve it going the other direction.

Let's say you have a named range BillJelenIsASpaceCowboy

You could type "BillJelenIsASpaceCowboy" into cell A1 and then put this formula where you'd like to know the address that BillJelenIsASpaceCowboy refers to:


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

 

[JenniferMurphy]

AFAIK, you can't do what you want to do in the direction you want to do it without using a UDF (VBA). However, having said that, you can certainly solve it going the other direction.

Let's say you have a named range BillJelenIsASpaceCowboy

You could type "BillJelenIsASpaceCowboy" into cell A1 and then put this formula where you'd like to know the address that BillJelenIsASpaceCowboy refers to:


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

Interesting and creative approach. Sadly, it doesn't really do what I need. The main problem is that if I change a name, the cell containing the name does not change.

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. I could put a formula like "=HeaderRow" in A1. In A2, I could put my GetFormula UDF, which would return that formula and then use some text editing to strip out all but the range name. And I understand that in later version of Excel, there is a built-in function to get the formula in a cell.

This reminds me of my days at IBM many years ago. They had a programming language named APL that was by far the most dense language I have ever encountered. It had its own character set and so required a special keyboard. They used to have informal competitions to see who could write the densest code. One such competition was the "one liner" competition where the goal was to solve some problem in a single like of code. I recall one guy writing a one-line function that would convert any number into Roman numerals. Ah, those were the days. Wasted youth. (sigh)