Can you "intersect" a multisheet (3-D) range name with a single sheetrange name?

[mr_unreliable]

hi group,

re: xl2k

I have a multi-sheet (3-D) range name specifying
(the same) column for all my sheets:

wbFY_2007 is defined as sheet1:sheet3!$G:$G

I have another range name specifying a row on the third
sheet:

AMT is defined as sheet3!$36:$36

I thought that the intersection operator would make an
intersection between the multisheet range and the single
sheet range, and come up with the AMT on sheet3 in column
G (2007).

The formula used was =(wbFY_2007 AMT), and the result produced
was #VALUE!.

Question: is it possible to "intersect" a multisheet (3-D)
range (specifying the same column on all the sheets) with a
single sheet range (specifying a row on one of the sheets)?
And if so, then how does one go about doing it.

cheers, jw

 

[Harlan Grove]

Question: is it possible to "intersect" a multisheet (3-D)
range (specifying the same column on all the sheets) with a
single sheet range (specifying a row on one of the sheets)?

....

No.

3D references are REFERENCES, not ranges. Intersection only works with
ranges. It's a picky technical point. 3D references, even named ones,
are only usable in a handful of functions, among them COUNT, SUM,
AVERAGE, etc. They can't be used any other way.

 

[T. Valko]

...

No.

3D references are REFERENCES, not ranges. Intersection only works with
ranges. It's a picky technical point. 3D references, even named ones,
are only usable in a handful of functions, among them COUNT, SUM,
AVERAGE, etc. They can't be used any other way.

Sort of on topic...

In Excel 2007 a few of the new *IFS functions make 3D calculations a little
easier.

=SUMPRODUCT(COUNTIFS(INDIRECT(List&"!A1:A10"),"x",INDIRECT(List&"!B1:B10"),"y",INDIRECT(List&"!C1:C10"),"z"))

List = named range of sheet names

 

[Harlan Grove]

In Excel 2007 a few of the new *IFS functions make 3D calculations a
little easier.

=SUMPRODUCT(COUNTIFS(INDIRECT(List&"!A1:A10"),"x",
INDIRECT(List&"!B1:B10"),"y",INDIRECT(List&"!C1:C10"),"z"))

List = named range of sheet names

So Excel 2007 still can't handle 3D references in COUNTIFS, SUMIFS and
AVERAGEIFS? Excel *does* have a well-defined 3D reference iterator,
i.e., an iterator that goes through 3D references in a predictable
manner. Excel's NPV function uses it. You'd think they could figure
out how to use them in COUNTIF etc.