Get data from sheet that contains a multible select drop down

Ida Lund

I have sheet 1 (S1), where I have a multible drop-down list with model-numbers + a Supplier 1-column. Here I have the data I need in Sheet 2 (S2).

In sheet 2 I have a row with model numbers, WHEN these matches with model-numbers in S1, I need the supplier-name in the row from S1 to go into the supplier-column in S2.

This will help me, so I only need to adjust the suppliers in S1 and S2-suppliers will automaticly be updated.

Best regards

Ida

Brian_Richardson

In S2 Supplier 1 column use this formula

=JOIN(COLLECT({S1-Supplier 1}, {Model}, CONTAINS(@cell,[Model nr.]@row)),CHAR(10))

{S1 - Supplier 1} is a reference to the Supplier 1 column on the first sheet. When entering the formula on the second sheet, click Reference Another Sheet in the formula popup helper box, browse to the first sheet, and click the Supplier 1 column header to select the column. Give that reference the name {S1 - Supplier 1} (or any name you want) and click Ok to insert the range reference. Repeat for {Model}, selecting the sheet 1 Model column.

Basically this formula is saying "collect a list for me of Sheet 1 supplier names, where the Model column next to that supplier contains the number in the Model nr. column next to me. Then, after collecting the list, separate the results with a line feed".

Also, set Word Wrap on the Sheet 2 Supplier 1 column so you can see if there's multiple results easily, with each result on a separate line. That's what the CHAR(10) part of the JOIN is doing…adding a new line for each result to the cell.