JOIN, COLLECT, Multi Select and Cross Sheet References

KROM

I have a sheet with a list of customers and items they have based on item type (multi select columns). I have another sheet that is an inventory of all possible items, item types, and item values (H-M-L). What I want to summarize by customer (row) is what are all the items they have that are considered "high" value based on the separate reference sheet.

Sheet 1: Item Selections

Sheet 2: Item Types

In other words, in Sheet 1 (Item Selection) I would want column "High Value Items" to join all selected values (Item Name) from columns "Animals", "Appliances", and "Instruments" that have an "Item Value" of "High" from Sheet 2 (Item Types). In this scenario, the cell in the first row would be blank because Avery has no high value items. The cell in the second row would read "Gas Range, Espresso Machine, Cello" because those are all the items Raven has that are classified as "high" value.

I've tried a number of different JOIN(COLLECT()) and IF and HAS statements and I haven't figured it out yet. Please help!

Brian_Richardson

Hi, use this as a column formula in the High Value Items column:

=JOIN(COLLECT({Item Name}, {Item Name}, HAS(Animals@row:Instruments@row, @cell), {Item Value}, "High"), ",")

The {} references are to your valuation sheet columns, inserted by clicking Reference Another Sheet while typing in the formula.

Here's what the results should look like.

Note that you can only get the results back as a comma separated text list, not as a multiple selection dropdown. Smartsheet doesn't support building multi-selection dropdown results through formulas. You have to employ a lot of Bridge trickery do that :-)

Brian_Richardson

Cool. I actually did just find out in another thread that you can return the results as a multiple-select dropdown column format if you want to. Replace the "," ending part of the JOIN with CHAR(10) and format the column as a multiple selection dropdown, word wrap it, and it will work.

=JOIN(COLLECT({Item Name}, {Item Name}, HAS(Animals@row:Instruments@row, @cell), {Item Value}, "High"), CHAR(10))

Like you said, though, not sure how that will play out in an Excel export. CHAR(10) is a line-feed character.

Brian_Richardson

Hi, use this as a column formula in the High Value Items column:

=JOIN(COLLECT({Item Name}, {Item Name}, HAS(Animals@row:Instruments@row, @cell), {Item Value}, "High"), ",")

The {} references are to your valuation sheet columns, inserted by clicking Reference Another Sheet while typing in the formula.

Here's what the results should look like.

Note that you can only get the results back as a comma separated text list, not as a multiple selection dropdown. Smartsheet doesn't support building multi-selection dropdown results through formulas. You have to employ a lot of Bridge trickery do that :-)

KROM

Thank you, @Brian_Richardson , that works perfectly!

As for getting formulas to deliver as multi select, I have a trick for that already. The comma delimiter is helpful for if I want to export this to excel ever. Thanks again!

Brian_Richardson

Cool. I actually did just find out in another thread that you can return the results as a multiple-select dropdown column format if you want to. Replace the "," ending part of the JOIN with CHAR(10) and format the column as a multiple selection dropdown, word wrap it, and it will work.

=JOIN(COLLECT({Item Name}, {Item Name}, HAS(Animals@row:Instruments@row, @cell), {Item Value}, "High"), CHAR(10))

Like you said, though, not sure how that will play out in an Excel export. CHAR(10) is a line-feed character.

Paul Newcome

Depending on the exact needs, the CHAR(10) / line break can be leveraged in Excel generally speaking to include text wrapping and parsing.