List from Join/Collect to return another values from another sheet

Morena

I have a join(Collect) list from another sheet in the cell. Now based on that list i want to use that join(collect) list to give me another join(Collect) list based on another sheet.

For example.

Sheet A

I pulled a list join(collection) list into Current Truck Status/s column. In this case, its two values Return - Uganda side & Songwe (R) - waiting for clearance. Now I want every item listed in the Current Truck Status/s column to return a list of Major Status/s based on Sheet B.

Sheet B

In this case I would expect a list of Available for imports allocations & Available for imports allocation in the Major Status/s column in Sheet A.

Please note Sheet B has items of 114, just cut them to 33 as you can see above.

I have used-

=IF(HAS([Current Truck Status/s]@row; "Songwe (R) - waiting for clearance"); INDEX({Major Status Mapping Range_Major}; MATCH("Songwe (R) - waiting for clearance"; {Major Status Mapping Range_Sub}; 0)) + CHAR(10))

This works but has limitations, where i couldn't add all items since they are 114.

Any suggestion on how else i can do this?

Paul Newcome

You would use another JOIN/COLLECT combo with a HAS function.

Morena

Hi Paul

Can you make me an example please

MichaelTCA

Hello,

You specify that in Sheet A, you expect to see a list of the same thing?

The HAS() function only evaluates ranges and not individual cells like the function you entered.

The function CONTAINS() will evaluate individual cells and ranges.

How come you are using CHAR(10)?

Changing the cell function to a column function should handle the rest of the rows.

Paul Newcome

@MichaelTCA The HAS function can be used to evaluate an entire range. I do it very regularly.

CHAR(10) is a line break. This is the default delimiter in multi-select dropdown type columns so that each piece brought in by the JOIN/COLLECT is separated by a line break and thus treated as a separate entry as opposed to having a single entry that is just one long string.

@Morena You are going to want something along the lines of

=JOIN(COLLECT({Major Status Column}, {Sub Status Column}, HAS([Current Truck Status/s]@row, @cell)), CHAR(10))

MichaelTCA

@Paul Newcome Yup. The function being presented is evaluating an individual cell and not a range.

Simpler syntax:

=IF([Current Truck Status/s]@row="Songwe (R) - waiting for clearance";INDEX({Major Status Mapping Range_Major}; MATCH([Current Truck Status/s]@row; {Major Status Mapping Range_Sub}; 0)) + CHAR(10))

The "TRUE" statement is also trying to match the string to a cell within the range. Since it's already evaluated in the condition, it's redundant to specify the same text in the MATCH function.

There is also no "FALSE" statement or IFERROR() function, unless I'm just missing it to evaluate return values that might be else values or errors.

I've used SUBSTITUTE with CHAR(10) to develop a list within a single cell. Seems excessive to use it row by row when there's a column function feature.

Paul Newcome

@MichaelTCA I had my response backwards when I was typing it out. Sorry. The HAS function can be used to evaluate single cells as well (also something I do on a very regular basis). The formula in the original post works just fine if you are evaluating a multi-select cell for something specific and wanting to bring in only one entry based on that specific option being selected.

If I have a multi-select dropdown with "A", "B", and "C" as my options, I could use the same syntax in the original post to create a formula that will run if (for example) "A" is selected regardless of whether it is the only selection or one of many selections.

=IF(HAS([Column Name]@row; "A"); "do this")

My formula will now "do this" as long as "A" is selected regardless of the other selections made. This is in fact the intent of the original post. To use this logic to create a series of IF statements to output the appropriate {Major Status} for every [Current Truck Status/s]@row selected in the multi-select dropdown.

This can work well enough if you only have a few options, but the challenge of the original post is not an issue with the syntax being off but the scalability of that particular method.

MichaelTCA

@Paul Newcome Interesting, I'll try it out some time!

@Morena Luckily, Smartsheet ramped up their cross link references to 500k this month. Using another Join/Collect function, like Paul said, might be the simplest way to go.

Morena

@Paul Newcome Thanx It worked!!!! You are a genius.

@MichaelTCA Thanx for your help. Really appreciate it.

Paul Newcome

Happy to help. 👍️