Lookup and concatenate all matching entries in closed workbook

Chinchin,

This is a common request. Mike Rickson published a great UDF for this, see this post for details:

http://www.excelforum.com/2593269-post9.html

Hope that helps,
~tigeravatar

EDIT: I originally stated that Mike Erickson published the UDF

I think that's Mike Rickson

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.

Please Login or Register  to view this content.

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.

Did some preliminary testing at lunch. It looks as if Excel performs as I expected. However the conversion from Range to Array will limit the kind of criteria the function will accept, fortunatly, the needs in the OP will be within that limitation.

Originally Posted by tigeravatar

Chinchin,

This is a common request. Mike Rickson published a great UDF for this, see this post for details:

http://www.excelforum.com/2593269-post9.html

Hope that helps,
~tigeravatar

EDIT: I originally stated that Mike Erickson published the UDF

My name is Mike Erickson. Not a politician, not from Oregon. Just a bloke with a quirky sense of spelling.

Here's the modified function. The arguments mirror those of SumIf and, as mentioned above, the criteria will compare equality only.

Please Login or Register  to view this content.