Unique dropdown values in cells from linked sheets?

anirudhs17

I want to create a unique dropdown menu based on a link from another sheet. 

On the linked sheet, one column will have a list of IDs and the second column will have a names that are associated with one type of ID. Each name has its own unique row, and IDs may appear multiple times for a group of names.

The linkee(?) sheet will have two columns: one with a dropdown for the IDs, and one with a dropdown that will contain the only names that are associated with the ID. So if an ID has 2 names associated, the drop down for the individual cell will have 2 values in it. Does anyone know if this is possible in Smartsheet?

Genevieve P.

Hi @anirudhs17

Dropdown columns have one set list of dropdown values for the entire column, meaning each cell in every row of that column will contain every possibility.

That said, you could create a reference column where it displays the options to choose from, and then use a formula to flag if the choice made was not an appropriate one for the current ID.

For example if you have the IDs listed down one column, you can then use a JOIN(COLLECT( formula to bring together all unique names into a Multi-select cell to visualize the Names for that ID. See: Formula combinations for cross sheet references

=JOIN(COLLECT({Names}, {IDs}, IDs@row), CHAR(10))

This way any time an ID is entered the possible values appear in the next column. The CHAR(10) at the end of the formula is what identifies that each Name should be an individual selection in a multi-select cell.

Then you could have a single select column to enter the selected value.

In my sheet I have a formula in the Flag "Incorrect Value" column:

=IF([Single Select]@row = "", "", IF(HAS([Names to Choose From]@row, [Single Select]@row), 0, 1))

You could then set up Conditional Formatting to highlight the row based on that flag.

Let me know if that will work for you or if you need help with either of the formulas!

Cheers,

Genevieve