The UDF I wrote, takes a Range argument (for the first and third arguments). For a closed workbook, I would re-write ConcatIf to take arrays for those arguments and hope that Excel will handle the conversion from (closed) sheet range to variant.
When I get back to my machine (with time to work). I would do a test with these two simple UDF's show the contrast I'm thinking of.
Then I would open two workbooks. Put those UDF's in Workbook1 and then put these formulas in cells of Workbook1
=myFunction([Workbook2.xlsm]Sheet1!$A$1)
=myOtherFunction([Workbook2.xlsm]Sheet1!$A$1)
do a little fiddling to see if everything works as it should. Then close Workbook2 and see if/how they behave.
That's how I'd start altering ConcatIf. By checking out how Excel processes references as it its passing arguments to a UDF.
Bookmarks