IFERROR(JOIN(COLLECT multiple sheets
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
-
Try "adding" them them together.
=IFERROR(JOIN(COLLECT(.................), " /// "), "") + IFERROR(JOIN(COLLECT(.................), " /// "), "") + IFERROR(JOIN(COLLECT(.................), " /// "), "")
Answers
-
Try "adding" them them together.
=IFERROR(JOIN(COLLECT(.................), " /// "), "") + IFERROR(JOIN(COLLECT(.................), " /// "), "") + IFERROR(JOIN(COLLECT(.................), " /// "), "")
-
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
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!