Get data from sheet that contains a multible select drop down
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
Answers
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!