Address of named range

[Guest]

Is there any worksheet function that returns the address (text) of a named
range? I have used the paste list menu option but require a worksheet
function to return the address

 

[Myrna Larson]

You probably need a VBA function for this. Here's one to get you started. To
use it, you need to put the name in quotes in the formula, i.e.

=NamedRangeAddress("MyRangeName")

It needs to be made much more robust. Right now it would work only for a
workbook-level name, and it returns something like

=Sheet1!$A$1:$A$10

Function NamedRangeAddress(sName As String) As String
On Error Resume Next
NamedRangeAddress = ThisWorkbook.Names(sName).RefersTo
End Function

 

[Frank Kabel]

Hi
for the 'first' cell of your range you could use
=CEL("address",your_range_name)

 

[Aladin Akyurek]

1.

=REPLACE(CELL("Address",(A1,List)),1,5,"")

where List is the named range of interest. The way the CELL() functio
behaves here is first reported by Harlan Grove.

2.

=CELL("Address",List)&":"&CELL("Address",INDEX(List,MATCH(2,1/(1-ISBLANK(List)))))

which must be confirmed with control+shift+enter instead of just wit
enter.

Obviously, [1] is more efficient/fast and also correct for the las
cell of List might be empty by intention.