Address of named range
 

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

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




On Fri, 12 Nov 2004 19:38:01 -0800, pcress
wrote:

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

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

--
Regards
Frank Kabel
Frankfurt, Germany

"pcress" schrieb im Newsbeitrag
...
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

1.

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

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

2.

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

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

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

pcress Wrote:
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


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=277943