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

...

...

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

...

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.

1. Named ranges, 3-D ranges, and/or arrays?

3. Conditional count in range w/#N?A's

4. Create Named Ranges (Headers) - then using range name in formu

5. Named ranges- how do I "combine" several into a larger named range?

6. Solution (was Named ranges- how do I "combine" several into a larger named range?)

7. Referencing a named range based upon Range name entry in cell

8. Create Named Ranges (Headers) - then using range name in formula

9. Named ranges scope / workbook/worksheet level named ranges- changes with variable use...

10. summing name range in the same column with Dynamic name ranges

11. Name range within a name range

12. If any cell in named range = 8 then shade named range

13. union of named ranges based only on the names of those ranges

14. Named ranges scope / workbook/worksheet level named ranges- change

15. Range Names associated with Sheet Name

4 post • Page:**1** of **1**