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

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

Post by mr_unrelia » Mon, 28 Jan 2008 03:31:32


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
 
 
 

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

Post by Harlan Gro » Mon, 28 Jan 2008 07:34:19

mr_unreliable < XXXX@XXXXX.COM > wrote...
...
...

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.

 
 
 

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

Post by T. Valk » Tue, 29 Jan 2008 07:54:34


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

--
Biff
Microsoft Excel MVP
 
 
 

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

Post by Harlan Gro » Tue, 29 Jan 2008 08:12:59

"T. Valko" < XXXX@XXXXX.COM > wrote...
...

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[S] etc.