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 (dynamic) list of all sheets, and then want to list the totals for the range intersection on each of "Data Count".
I kept getting a #Ref error on evaluating the indirect function which combines the sheet and range names, so I have tried a variety of methods, and concluded that there is a restriction in Indirect that it cannot use a range definition that is an intersection of two named ranges.
Examples: (Cell B4 contains the sheet name)
=COUNTIF(INDIRECT("'"&$B4&"'!"&"Data Status"),"=Flag") -- Fails #Ref
Substituting a manual specification for the same range:
=COUNTIF(INDIRECT("'"&$B4&"'!$F7:$F40"),"=Flag") -- Works
Forcing indirect first on each range, and then the intersection:
=COUNTIF(INDIRECT("'"&$B4&"'!"&"Data") INDIRECT("'"&$B4&"'!"&"Status"),"=Flag") -- Works
Or simplifying:
=COUNTIF(INDIRECT($B4&"!Data") INDIRECT($B4&"!Status"),"=Flag") -- Works
From this I conclude that while [I]indirect [/I]works with named ranges, that is only with simple single-named ranges, not intersections.
Is this accurate?
I have found several other examples like this where there are restrictions on range naming usages, but never seen any of them documented by MSoft.
2 Replies
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.
This is not specific to INDIRECT. For worksheet-scope names, you have to specify the sheet explicitly for each name.
The formula
=COUNTIF(MySheet!Data Status,"=Flag")
will return #NAME!, while
=COUNTIF(MySheet!Data MySheet!Status,"=Flag")
will work.
