External References in Functions
Thanks. This helps.
-Greg
"Harlan Grove" wrote:
Ferencko wrote...
I'm having trouble with an external reference in one of my Excel
Functions. The general form of my function is:
=COUNTIF( external reference to a range, a value)
This is a FORMULA that calls the COUNTIF function.
When I open my worksheet and tell it to update the data, the cells
with this formula indicate that there is an error with my function.
But then, when I open the workbook containing the external reference,
my formulas miraculously correct themselves.
....
That's because COUNTIF requires range references as first argument,
and ranges as instantiated Excel objects can only exist in OPEN
workbooks. Excel evaluates external references into closed workbooks
as arrays. That means when the other workbook is open, Excel can pass
COUNTIF a reference to that range; but when the other workbook is
closed, Excel can only pass an array to COUNTIF. To COUNTIF, that's a
critical difference.
If you may be referring to, er, blocks of cells in closed workbooks,
you need to use SUMPRODUCT rather than COUNTIF. Using your example
above, change your formulas to
=SUMPRODUCT(--( external reference to a range = a value ))
This has been a known problem with COUNTIF (and SUMIF) for years.
There's no work-around using COUNTIF. You have to use a different
function when referring to possibly closed workbooks.
|