# IFERROR(JOIN(COLLECT multiple sheets

Options

Hi, I am trying to build a formula in a sheet with a list of order numbers ("global order list" sheet) to search collect and join the product details contained in three different sheets ("EMEA product details", "APJ product details" and "Americas product details" sheets).

In the "regional" sheets the order number can appear several times with different products, that's why I am using a join-collect formula.

I came up with a first draft (see below) which does not work correctly because while the formula gives a value for EMEA orders, it does not give any value for APJ ones even if I have some APJ orders in the "global order list sheet" and have the product details in the "APJ product details" sheet .

=IFERROR(JOIN(COLLECT({EMEA - Product}, {EMEA - Order#}, =[Order#]@row), " /// "),

IFERROR(JOIN(COLLECT({APJ - Product}, {APJ - Order#}, =[Order#]@row), " /// "),

IFERROR(JOIN(COLLECT({Americas - Product}, {Americas - Order#}, =[Order#]@row), " /// "), "")))

Thanks!

Stan

• ✭✭✭✭✭✭
Options

=IFERROR(JOIN(COLLECT(.................), " /// "), "") + IFERROR(JOIN(COLLECT(.................), " /// "), "") + IFERROR(JOIN(COLLECT(.................), " /// "), "")

• ✭✭✭✭✭✭
Options

=IFERROR(JOIN(COLLECT(.................), " /// "), "") + IFERROR(JOIN(COLLECT(.................), " /// "), "") + IFERROR(JOIN(COLLECT(.................), " /// "), "")

• Options

Hi Paul, it seems to work. I will have to run some tests, also because I think that the same order number can be used in different regions, and also will need to do some adjustments because if the order number is not present the cell is filled with the delimiter

Anyway thank you very much for your help :)

Stan

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!