3D array

[Michel Cinq-Mars]

What I have is this code:

=SUMPRODUCT('[Workorders.xls]MCM-13:MCM-14'!$M:$M=D2*'[Workorders.xls]MCM-13
:MCM-14'!$J:$J=A21,'[Workorders.xls]MCM-13:MCM-14'!$K:$K)

Workorders is another file.
MCM-13 & MCM-14 are the 2 worksheets in that file (I will need to expand to
more then this)
D2 is the Job ID I am looking for.
Column M contains the list of jobs.
A21 is the Date I am looking for.
Column J contains the date worked on that job.
Column K contains the cost for the work.

When I enter this formula it returns #REF!.
Have I missed something or can I not check more then one sheet at one time?

It's a big one so lots of thanks in advance!

Michel Cinq-Mars

 

[Harlan Grove]

What I have is this code:

=SUMPRODUCT('[Workorders.xls]MCM-13:MCM-14'!$M:$M=D2*
'[Workorders.xls]MCM-13:MCM-14'!$J:$J=A21,
'[Workorders.xls]MCM-13:MCM-14'!$K:$K) ....
When I enter this formula it returns #REF!.
Have I missed something or can I not check more then one sheet at one time?

Only a few functions accept 3D references, and SUMPRODUCT isn't one of them.
Also, even fewer functions (and not the same ones) accept entire column
references like J:J.

What are the actual ranges on these worksheets that you'd be using?

 

[Michel Cinq-Mars]

Well that's the problem, the range changes.
I guess I could limit it down to 50 rows but I hate to restrict it.
The number of worksheets will constantly increase.
Is there some way I can Array enter it?

Thanks,

Michel Cinq-Mars

What I have is this code:

=SUMPRODUCT('[Workorders.xls]MCM-13:MCM-14'!$M:$M=D2*
'[Workorders.xls]MCM-13:MCM-14'!$J:$J=A21,
'[Workorders.xls]MCM-13:MCM-14'!$K:$K) ...
When I enter this formula it returns #REF!.
Have I missed something or can I not check more then one sheet at one

time?

Only a few functions accept 3D references, and SUMPRODUCT isn't one of them.
Also, even fewer functions (and not the same ones) accept entire column
references like J:J.

What are the actual ranges on these worksheets that you'd be using?

 

[Harlan Grove]

Well that's the problem, the range changes.
I guess I could limit it down to 50 rows but I hate to restrict it.
The number of worksheets will constantly increase.
Is there some way I can Array enter it?

Laurent Longré's MOREFUNC.XLL add-in contains a function called THREED that
converts 3D references to 2D arrays by stacking the ranges in each worksheet
vertically. For example,

Sheet1!A1:B4
a b
c d
e f
g h

Sheet2!A1:B4
i j
k l
m n
o p

Sheet3!A1:B4
q r
s t
u v
w x

THREED(Sheet1:Sheet3!A1:B4) returns

a b
c d
e f
g h
i j
k l
m n
o p
q r
s t
u v
w x

However, the return array would be limited to 65535 entries in total, so you
couldn't feed it entire columns (which may be grossly inefficient in SUMPRODUCT
formulas even if you could). However, if these 3D ranges involve fewer than 65
worksheets, and single column ranges in each, you could use

=SUMPRODUCT(THREED('[Workorders.xls]MCM-13:MCM-14'!$M$1:$M$1000)=D2*
THREED('[Workorders.xls]MCM-13:MCM-14'!$J$1:$J$1000)=A21,
THREED('[Workorders.xls]MCM-13:MCM-14'!$K$1:$K$1000))

If this would be acceptable, you can down load MOREFUNC.XLL from

http://longre.free.fr/english

though I believe there's also a French version, which you may prefer.