Array, multiple sheets

[Ronny]

Hi all,

I'm looking at doing some "special" SUM & IF, and I know the
formula I want if it all had been in one sheet. But when trying to go
across worksheets I run into problems.

If on one sheet I would check column L for a criteria (=1 in example
below, or a cell ref. in real life), if criteria is right I will add
column H * column I and summarize the total. This is the formula I
would use:
{=SUM(IF(L2:L10=1,H2:H10*I2:I10,0))}

Now I want to combine this with the possibility of making formulas like
these:
=SUM(Sheet1:Sheet10!B3)

So I want to check cell B1 on Sheet1 to 10 is equal to B1 on the
current sheet, if so multiply B2 and B3 on that sheet and in the end
add up all the ones that meet the "IF" requirement. I was thinking
of a formula looking something like this:
{=SUM(IF(Sheet1:Sheet10!B1=B1,Sheet1:Sheet10!B2*Sheet1:Sheet10!B3,0))}
This gives #REF!

I've tried to create a couple of other array formulas across
worksheets, and I can't get any of them to work.

Is it not possible to use arrays { } (ctrl + shift + enter) across
worksheets? Or is it just something I'm missing that must be included
when working across worksheets that I can add and everything is
working?

If this is not possible through an array formula, can anyone start
pointing me in the right direction for creating a custom user function
in VBA for this?

I'd be happy for any help on this since it's been bugging me for a
couple of days now.

Ronny

 

[Tom Ogilvy]

Array formulas do not work across sheets in the fashion you envision.

You can have an array formula for each sheet and sum the results.

 

[Ronny]

Thank you for your quick reply.

That's what I was afraid of. Well, there is only one cell * one cell
on each sheet, so an array would not be needed there.
But that would mean I would do =IF(Sheet1!B1=B1,Sheet1!B2*Sheet1!B3,0)+
IF(Sheet2!B1=B1,Sheet2!B2*Sheet2!B3,0)+
IF(Sheet3!B1=B1,Sheet3!B2*Sheet3!B3,0)+... etc. and I would very
quickly run out of the number of IFs I can use. And it has no
flexibility as to insert more sheets etc.

Do you know of any other way? Or is it possible to create something in
VBA? I'm not really sure how VBA deals with ranges across worksheets.

Ronny

 

[Tom Ogilvy]

My contention is that VBA does not support 3D arguments in UDF's. However,
someone may have better ideas than I do.

In the past I have posted a solution that parses the argument as a string
and resolves it, so if no one comes up with something better, you can use
that approach as shown at the URL:.
http://tinyurl.com/c6oo9