Forum Discussion

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

Iron Contributor

Oct 02, 2022

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

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.