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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mr_unreliable - 26 Jan 2008 18:29 GMT

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

Reply to this Message

Harlan Grove - 26 Jan 2008 22:34 GMT

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.

Reply to this Message

T. Valko - 27 Jan 2008 22:54 GMT

> mr_unreliable <kindlyReplyToNewsgr...@notmail.com> wrote...
> ...
[quoted text clipped - 9 lines]
> 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

Signature

Biff
Microsoft Excel MVP

Reply to this Message

Harlan Grove - 27 Jan 2008 23:12 GMT

"T. Valko" <biffinp...@comcast.net> wrote...
...

>In Excel 2007 a few of the new *IFS functions make 3D calculations a
>little easier.
[quoted text clipped - 3 lines]
>
>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.

Reply to this Message