Named Manager that is not a Named Range

New Contributor

I am used in creating Named Ranges on old version of Excel, and you can use it with an INDIRECT function.  With the new version, we can use Named Manager to create formula, instead of just named ranges.  However, the indirect function will not work.  I also use it VBA where you can determine your list as Range("myList"), where myList as Named Range = Sheet1!A1:B10.

 

How can I refer to a Named Manager item with formula using INDIRECT or VBA with Range?  For example, I named a myNewList = LET(n,10,i,SEQUENCE(n)).  I know I can just use =myNewList, instead of =INDIRECT("myNewList"), but can I do on VBA? How do I reference myNewList into an Array?

 

Please help!!!

2 Replies
Have not tried myself. Does Range("myNewList#") not work? (I have a feeling you are omitting the "#".)

@ecovonrein, Unfortunately INDIRECT("myList#") does not work.  As I understood it,  @ refers to the first item of the range, array, or named item; and # refers to the entire range.  If I use the =myList in A1, I can do INDIRECT("A1#") and it will work since it referencing a range.  If I just do INDIRECT("A1") or @myList, it will just give me 1.  However, the idea of named ranges (manager) is to use natural language concept; I want the name to be meaningful, but at the same time I don't want loaded in the sheet if I don't need to display it.  I even tried =myList# and it gives me errors, since myList is not a range, due to the fact that ranges work with INDIRECT.  I am guessing myList is an array (without a range reference), since it works with SUMPRODUCT that uses array as parameters.  FYI, @{1,2,3,4} = 1, but @myList# does not work.