Excel indirect reference to intersection of named ranges on different sheet

[Gregory Guthrie]

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]

@Gregory Guthrie 

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.

[#3]

@Gregory Guthrie 

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.