is there a way to get the definition of a defined name into an excel cell?

[TomCon]

I want to get the definition of a defined Name into an excel cell.

So, if i have a name, MyName, and it is defined as Sheet1!A1:A10, i want to get the text string "Sheet1!A1:A10" into an Excel cell.

=MyName does not do it.
Also =T(MyName) does not do it.

Is there a way?

Thanks!

 

[Fluff]

How about

=CELL("address",MyName)

 

[TomCon]

How about

=CELL("address",MyName)

It turns out that this returns the address of the first cell, but not the text of the definition. So, if MyName is "Sheet1:A1:A10" you will get [workbook]Sheet1!A1. But, thanks for the response.

If anybody has thoughts on how to get the whole Defined Name definition into a cell, please post!

Thanks!

 

[Fluff]

Why do you need to get the refers to address in a cell?

 

[Peter_SSs]

Use a user-defined function?

Function Refers_To(strName As String) As String
  Dim nm As Name
  
  For Each nm In ThisWorkbook.Names
    If UCase(nm.Name) = UCase(strName) Then
      Refers_To = Mid(Replace(nm.RefersTo, "$", ""), 2)
      Exit For
    End If
  Next nm
End Function

TomCon.xlsm
	A
1	Sheet1!A1:A10
Sheet2

Cell Formulas
Range	Formula
A1	A1	=Refers_To("MyName")

 

[bebo021999]

A bit longer, but it works:

=MID(CELL("filename",myName),SEARCH("]",CELL("filename",myName))+1,255)&"!"&CELL("address",myName) & ":" &
 ADDRESS(AGGREGATE(14,6,ROW(myName),1),AGGREGATE(14,6,COLUMN(myName),1))

 

[BSALV]

add an empty sheet or select a range that is big enough for all the used names.
then in the ribbon Formulas>use in formula>Paste names>Paste Liste (hope it's the right translation)

 

[Peter_SSs]

i want to get the text string "Sheet1!A1:A10" into an Excel cell

add an empty sheet or select a range that is big enough for all the used names.
then in the ribbon Formulas>use in formula>Paste names>Paste Liste (hope it's the right translation)

As I read it, that doesn't quite produce the result as asked for - and may produce a long list of results.

A bit longer, but it works:

=MID(CELL("filename",myName),SEARCH("]",CELL("filename",myName))+1,255)&"!"&CELL("address",myName) & ":" &
 ADDRESS(AGGREGATE(14,6,ROW(myName),1),AGGREGATE(14,6,COLUMN(myName),1))

Well, I don't think it quite works as requested (at least it didn't for me) as shown in D1 below, but using your idea the modified/shortened version in D2 seems to.

TomCon.xlsm
	D
1	Sheet1![TomCon.xlsm]Sheet1!$A$1:$A$10
2	Sheet1!A1:A10
Sheet2

Cell Formulas
Range	Formula
D1	D1	=MID(CELL("filename",MyName),SEARCH("]",CELL("filename",MyName))+1,255)&"!"&CELL("address",MyName) & ":" & ADDRESS(AGGREGATE(14,6,ROW(MyName),1),AGGREGATE(14,6,COLUMN(MyName),1))
D2	D2	=LET(s,CELL("address",MyName) & ":" &ADDRESS(AGGREGATE(14,6,ROW(MyName),1),AGGREGATE(14,6,COLUMN(MyName),1)),SUBSTITUTE(REPLACE(s,1,FIND("]",s),""),"$",""))

 

[TomCon]

add an empty sheet or select a range that is big enough for all the used names.
then in the ribbon Formulas>use in formula>Paste names>Paste Liste (hope it's the right translation)


View attachment 70106

yes, this works. What an obscure hidden place, but thanks! Gives just the list i need.

 

[TomCon]

As I read it, that doesn't quite produce the result as asked for - and may produce a long list of results.


Well, I don't think it quite works as requested (at least it didn't for me) as shown in D1 below, but using your idea the modified/shortened version in D2 seems to.

TomCon.xlsm
	D
1	Sheet1![TomCon.xlsm]Sheet1!$A$1:$A$10
2	Sheet1!A1:A10
Sheet2

Cell Formulas
Range	Formula
D1	D1	=MID(CELL("filename",MyName),SEARCH("]",CELL("filename",MyName))+1,255)&"!"&CELL("address",MyName) & ":" & ADDRESS(AGGREGATE(14,6,ROW(MyName),1),AGGREGATE(14,6,COLUMN(MyName),1))
D2	D2	=LET(s,CELL("address",MyName) & ":" &ADDRESS(AGGREGATE(14,6,ROW(MyName),1),AGGREGATE(14,6,COLUMN(MyName),1)),SUBSTITUTE(REPLACE(s,1,FIND("]",s),""),"$",""))

Yes, this does work to get a single name but the paste list below gets them all which is what i need. Thanks!