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), " /// "), "")))


Can you please help?

Thanks!

Stan

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!