INDEX(INDIRECT("DefinedName"),1)=#REF while INDEX(DefinedName,1) works
excel, excel-formula
Solution
Thomas,
I hadn't come accross this before.
It appeas that `INDIRECT` and dynamic range names are incompatible. There is a useful reference here from Dicks blog
Problem
In an excel workbook, I'm referencing a defined name from another tab (to get dependent data validation). Accessing the defined name directly works, but accessing it through `INDIRECT` doesn't. `=INDEX(DefinedName,1)` returns the first value of the range `=INDEX(INDIRECT("DefinedName"),1)` returns #REF! I have also tried `=INDEX(INDIRECT("SheetName!DefinedName"),1)`, but it also returns #REF! Sample file can be downloaded here.