Unfortunately not.

I replaced the ActiveCell with Application.Caller but it didn't make any
difference

I also added Application.Volitile (True) as suggested elsewhere on the net.

Any other ideas.

Thanks





"Niek Otten" wrote:

I suppose Bob's answer solved your problem?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Cresta" wrote in message
...
=Sum2DProduct(Header1Range ,Header1Criteria ,Column1Range ,Column1Criteria
,DataRange )

At the moment we use an array formula to do the searching throught a mass
of
data and it takes quite a long time to do it. I have been asked to see if
a
UDF can be developed to replace it and speed it up. The UDF below works
when
the file is the active file and is much quicker. But it falls over when it
is
not the active file and is being calculated using F9 calculating all open
workbooks.

We have looked at using the new SumIfs but I think I am correct in saying
it
only works in one direction, although with many criterias along that
direction.
We need to scan along the top of the data to match the month (may be more
that one column) then down the codes on the right to match a code number
(may
be more than one row). Then return the sum of the cells identified.

The logic of returning the data isn't the issue, it's the inability to
have
the UDF perform when it isn't the active file.

Hope this helps
Any ideas





"Niek Otten" wrote:

What's the code of the function and how do you call it? What are the
values
of the arguments?
What do you think it should return and why?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Cresta" wrote in message
...
Hello All and Help! (Hope this makes sence)

=Sum2DProduct(ProfitMons,C$8,ProfitCodes,$BD11,Pro fit)
All ranges are within the same file.

I have the above custom function that works well when the file is
active.
However, when a different file is active then the custom function
returns
a
value of '0'.

I'm guessing the function isn't referencing anything to do with the
file
it
is in, only the ranges selected for the funtion. And is applying itself
to
the wrong active file.

How do I get the function to apply itself to the file it belongs to.

Thanks