Forum Discussion

inZAHNe's avatar
inZAHNe
Copper Contributor
Sep 30, 2022

Named Manager that is not a Named Range

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 rang...
excel
Formulas and Functions
Macros and VBA
ecovonrein's avatar
ecovonrein
Iron Contributor
Oct 02, 2022
Have not tried myself. Does Range("myNewList#") not work? (I have a feeling you are omitting the "#".)
inZAHNe's avatar
inZAHNe
Copper Contributor
Oct 02, 2022

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.

Resources