Forum Discussion
Excel indirect reference to intersection of named ranges on different sheet
I have a workbook with a number (expandable) of different data sheets. I have a standard format for each, and each has local named ranges of "data" and "status". On a front Summary sheet I make a (...
In your example, you have intersected the direct references before offering it as a parameter to INDIRECT, so it works. For the names, you have left the two strings with a space between. This first evaluates to
'Mysheet'!Data Status
and then INDIRECT fails to convert it to a range. Even the old XLM function EVALUATE struggles because it will look for 'Status' on the active sheet or as a workbook name.
I haven't used direct cell referencing for 6 years now and have yet to find limitations. More the reverse, in the past, calculation within Name Manager used to more effective than calculation on the grid.