Is there a way to obtain the name of a range?

[JenniferMurphy]

Suppose I want to show the names of various named ranges in a table. In this table, for example, I have assigned the name "Price" to Column C and "Quan" to Column D. I would like to put some expression, like "=RangeName(C:C)", into C6 and D6 that would show those names and would change if I renamed those ranges.

[TABLE="class: grid, width: 420"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Unit Price[/TD]
[TD="align: center"]Quantity[/TD]
[TD="align: center"]Cost[/TD]
[TD="align: center"]Formula[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"](Price)[/TD]
[TD="align: center"](Quan)[/TD]
[TD="align: center"]()[/TD]
[TD="align: center"]()[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]$9.98[/TD]
[TD="align: center"]250[/TD]
[TD="align: right"]$2,495.00[/TD]
[TD]E7: =Price*Quan[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]$1.29[/TD]
[TD="align: center"]500[/TD]
[TD="align: right"]$645.00[/TD]
[TD]E8: =Price*Quan[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]$5.49[/TD]
[TD="align: center"]130[/TD]
[TD="align: right"]$713.70[/TD]
[TD]E9: =Price*Quan[/TD]
[/TR]
</tbody>[/TABLE]

If this does not exist, can I write a UDF that will do the job?

Thanks

 

[DanteAmor]

General observation about some of the code that has been posted...

1) When using Application.Caller, the cell it references is the cell the UDF formula is in (which may or may not be within the sought after named range).

2) You should not use ActiveCell within a UDF formula as I don't think it will report correctly.

3) Any cell or range of cells can be part of more than one Named range (although that is probably not the case for the OP's setup).

The following UDF is probably not going to be useful to the OP... what it does is take a single cell reference (a #REF ! error is returned if a multiple cell range is passed into it) and return the name of every Named range the cell is part of (as a comma delimited text string)...

Hi Rick, I guess, you did not have the opportunity to try all the solutions of tetra201 and mine, they all work, but Jennifer does not want the range as an argument, as she says in her posts # 11, # 16 and # 17.
I hope that now it works for her. Good luck.

 

[shg]

I don't understand what RC3 and RC4 are.

R1C1 definitions; Price is the cell in the same row in col C, and Quan is the cell in the same row in col D, as we discussed in a prior thread.