Excel indirect reference to intersection of named ranges on different sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2694142%22%20slang%3D%22en-US%22%3EExcel%20indirect%20reference%20to%20intersection%20of%20named%20ranges%20on%20different%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2694142%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20with%20a%20number%20(expandable)%20of%20different%20data%20sheets.%3C%2FP%3E%3CP%3EI%20have%20a%20standard%20format%20for%20each%2C%20and%20each%20has%20local%20named%20ranges%20of%20%22data%22%20and%20%22status%22.%3C%2FP%3E%3CP%3EOn%20a%20front%20Summary%20sheet%20I%20make%20a%20(dynamic)%20list%20of%20all%20sheets%2C%20and%20then%20want%20to%20list%20the%20totals%20for%20the%20range%20intersection%20on%20each%20of%20%22Data%20Count%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20kept%20getting%20a%20%23Ref%20error%20on%20evaluating%20the%20indirect%20function%20which%20combines%20the%20sheet%20and%20range%20names%2C%20so%20I%20have%20tried%20a%20variety%20of%20methods%2C%20and%20concluded%20that%20there%20is%20a%20restriction%20in%20Indirect%20that%20it%20cannot%20use%20a%20range%20definition%20that%20is%20an%20intersection%20of%20two%20named%20ranges.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExamples%3A%20(Cell%20B4%20contains%20the%20sheet%20name)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIF(INDIRECT(%22'%22%26amp%3B%24B4%26amp%3B%22'!%22%26amp%3B%22Data%20Status%22)%2C%22%3DFlag%22)%26nbsp%3B%26nbsp%3B%20--%20Fails%20%23Ref%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESubstituting%20a%20manual%20specification%20for%20the%20same%20range%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIF(INDIRECT(%22'%22%26amp%3B%24B4%26amp%3B%22'!%24F7%3A%24F40%22)%2C%22%3DFlag%22)%26nbsp%3B%26nbsp%3B%20--%20Works%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EForcing%20indirect%20first%20on%20each%20range%2C%20and%20then%20the%20intersection%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIF(INDIRECT(%22'%22%26amp%3B%24B4%26amp%3B%22'!%22%26amp%3B%22Data%22)%20INDIRECT(%22'%22%26amp%3B%24B4%26amp%3B%22'!%22%26amp%3B%22Status%22)%2C%22%3DFlag%22)%26nbsp%3B%26nbsp%3B%20--%20Works%3C%2FP%3E%3CP%3EOr%20simplifying%3A%3C%2FP%3E%3CP%3E%3DCOUNTIF(INDIRECT(%24B4%26amp%3B%22!Data%22)%20INDIRECT(%24B4%26amp%3B%22!Status%22)%2C%22%3DFlag%22)%26nbsp%3B%26nbsp%3B%20--%20Works%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20this%20I%20conclude%20that%20while%20%5BI%5Dindirect%20%5B%2FI%5Dworks%20with%20named%20ranges%2C%20that%20is%20only%20with%20simple%20single-named%20ranges%2C%20not%20intersections.%3C%2FP%3E%3CP%3EIs%20this%20accurate%3F%3C%2FP%3E%3CP%3EI%20have%20found%20several%20other%20examples%20like%20this%20where%20there%20are%20restrictions%20on%20range%20naming%20usages%2C%20but%20never%20seen%20any%20of%20them%20documented%20by%20MSoft.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2694142%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2694654%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20indirect%20reference%20to%20intersection%20of%20named%20ranges%20on%20different%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2694654%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F141261%22%20target%3D%22_blank%22%3E%40Gregory%20Guthrie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20not%20specific%20to%20INDIRECT.%20For%20worksheet-scope%20names%2C%20you%20have%20to%20specify%20the%20sheet%20explicitly%20for%20each%20name.%3C%2FP%3E%0A%3CP%3EThe%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCOUNTIF(MySheet!Data%20Status%2C%22%3DFlag%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewill%20return%20%23NAME!%2C%20while%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCOUNTIF(MySheet!Data%20MySheet!Status%2C%22%3DFlag%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewill%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

@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.

@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.