INDIRECT.EXT returns #VALUE

In XL2007:

Thanks to this forum I learned about INDIRECT.EXT. I'm stumped, though, when it returns the #VALUE error. If I use Formula Auditing, Evaluate Formula, to step through an AVERAGEIF function referring to a named range, each step but the last proceeds without error. The final evaluation of the function shows an array retrieved from the closed file. [Error is independent of settings for volatile and style.] The calculation is correct, though, if the file is open (it shows a range rather than an array during evaluation)!

If I perform the same steps on an otherwise identical function using the original INDIRECT function the final step shows a range being retrieved rather than an array.

Because I use a set of file names where the named range may have different addresses it is not possible to convert the formula to A1 style addressing. edit: Using a range, e.g., B13:O13 also returns an array when file is closed, so this is not a solution anyway

Any clues on how to get the two functions to behave identically?

Thanks.

g

AVERAGEIF like SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIFS will not work with closed targets
(ie - this is not related to INDIRECT.EXT per se)

You should revert to an Average Array or SUMPRODUCT/SUMPRODUCT - personally I'd opt for the former.

Thanks for your reply. Based on this, I threw this together. It meets my needs anyway:

Please Login or Register  to view this content.

geoB, please ensure you surround your VBA with CODE tags at all times - please edit your prior post accordingly.

Might also be an idea to outline how you are calling the UDF - presumably you are using INDIRECT.EXT to generate your Variant Array ?