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

mr_unreliable <kindlyReplyToNewsgr...@notmail.com> wrote...
....
>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.

"Harlan Grove" <(E-Mail Removed)> wrote in message
news:ba33a2b2-aa48-4f7f-91e5-(E-Mail Removed)...
> mr_unreliable <kindlyReplyToNewsgr...@notmail.com> wrote...
> ...
>>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.

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

"T. Valko" <biffinp...@comcast.net> wrote...
....
>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[S] etc.