Removing semicolon and pull multiple values from reference sheet

Christina09

Hi,

I have two sheets: Sheet 2 (reference sheet, see photo 1) and Sheet 1 (formula sheet, see photo 2). I've created a formula that works when the item number column is a dropdown menu with multiple selections. However, I need a formula that achieves the same results when the item numbers are separated by semicolons ";" in a text/number format (see photo 3).

Current formula:

=JOIN(DISTINCT(COLLECT({item name}, {item number}, HAS([Item Number]@row, @cell))), CHAR(10))

Darren Mullen

@Christina09 What @Adam Costello suggested would be the most accurate since you can do an exact match.

Otherwise you can make it work with Contains, but it's possible you may get erroneous matches if your item numbers get longer and contain the shorter number.

Formula:

=JOIN(COLLECT({Item Sheet | Item Name}, {Item Sheet | Item Number}, CONTAINS(@cell, [List of Item Numbers SemiColons]@row)), CHAR(10))

Screenshot:

@Michelle Choate 2 Thanks for the tag

Michelle Choate 2

Hey @Darren Mullen - How would you approach this one? You are the Smartsheet Guru and can usually think of a great formula because this one has me stumped. I am not sure there even is a way. What are you thinking?

Adam Costello

I'm working on doing it without a helper column but what you can do is make a dropdown helper column to convert your semi colon cells to a multi-select cell and then use your original formula referencing the helper column.

=SUBSTITUTE([Item Number]@row, ";", CHAR(10))

Darren Mullen

@Christina09 What @Adam Costello suggested would be the most accurate since you can do an exact match.

Otherwise you can make it work with Contains, but it's possible you may get erroneous matches if your item numbers get longer and contain the shorter number.

Formula:

=JOIN(COLLECT({Item Sheet | Item Name}, {Item Sheet | Item Number}, CONTAINS(@cell, [List of Item Numbers SemiColons]@row)), CHAR(10))

Screenshot:

@Michelle Choate 2 Thanks for the tag

Christina09

@Michelle Choate 2 @Adam Costello @Darren Mullen

This works great! Thank you Champions for helping!! 🤩