Set dropdown based on values in row

SCSDmoore

Hello, I'm trying to set values in a dropdown list using a formula (or any other suggestions) based on the columns in the same row.

For example, I have a drop down list titled Cities that allows multi select with the values Chicago, Boston, Atlanta as options.

I have three columns on the same sheet named Chicago, Boston, Atlanta. In each row, the word Yes or No is added for each city. I'd like to summarize that into check boxes in the drop down.

So if Chicago, Boston and Atlanta are all yes, I'd like the drop down to have all three cities checked.

If Chicago is set to yes, but Boston and Atlanta are No, I'd like the drop down to have a check in Chicago and no checks in Boston and Atlanta.

Thanks for any advice.

Heather_Verde

If your formula works as you expect it to on your current sheet, you can maintain the criteria to select on a separate sheet. Just replicate your Boston, Chicago, and Atlanta column headers and the selections to use in row 1 on a separate sheet. Then in the formula, replace the first portion with the range from the other sheet (you can use the Reference Another Sheet link when entering the range and navigate to the reference sheet and select the exact cells as you would have in row 1. So your formula would look something like this:

=JOIN(COLLECT({Separate Sheet Range},[FIRST COLUMN]@row:[SECOND COLUMN]@row,<>""),CHAR(10))

Then you can delete row 1 on your current sheet and allow the data shuttle to process.

Mary_A

If you search the community in google like this:

This answer comes up: https://community.smartsheet.com/discussion/6888/using-percent-value-to-set-dropdown

Try modifying this formula for your use case. It might do the trick.

Though, you are missing a use case which is two out of the tree are checked. What would you like to happen in that case?

It might be more useful to adjust your sheet design rather than to solve this with a complex formula. If you want to share a copy of your sheet via a public link, that is always helpful when you ask a question in the community.

Heather_Verde

I believe you can find your answer here - https://community.smartsheet.com/discussion/67320/automate-the-selection-of-values-from-a-multi-select-dropdown. You would need a "helper row" like row 1 to put the value of the cities in. So for example in row 1 under each of the columns you have for Boston, Chicago, and Atlanta, you have the name of the city again, and then in the subsequent rows you have your yes/no options.

Have your 3 columns next to each other "Boston", "Chicago", "Atlanta". In your "Cities" column, you would put a formula to look at the options in row 1 under Boston, Chicago, and Atlanta and join the results based on the answer provided in the row you are evaluating.

=JOIN(COLLECT([Boston]$1:[Atlanta]$1, [Boston]@row:[Atlanta]@row, "yes"), CHAR(10))

breakdown of what it means =JOIN(COLLECT(my results to select range, my range within the row to evaluate, the criteria to select what's in my select range), spacing)

SCSDmoore

Thank you both for the help.

@Mary_A, I need to check around 20 columns, and then depending on if they hold data, I need to multiselect items in the dropdown column.

@Heather_Verde, Thanks for pointing me towards the COLLECT function.

Ideally my "results to select range" would be the column headers. I've tried to create a list of the "results to select" in another sheet, thinking I could reference it, but haven't been successful yet (not ruling that idea out yet). I've finally just matched the column headers in row 1, and that's working!

For the "criteria to select" I am checking if the cell is empty or <>""

Here's what the formula looks like now

=JOIN(COLLECT([FIRST COLUMN IN CRITERIA TO SELECT]$1:[SECOND COLUMN IN CRITERIA TO SELECT]$1,[FIRST COLUMN]@row:[SECOND COLUMN]@row,<>""),CHAR(10))

Any idea if I can use the column headers instead of row 1? This data is going to be overwritten nightly with data shuttle, and while I'm only updating or adding to the data, I don't want to worry about someone deleting the row. I suppose I could hide it? Maintaining the "criteria to select" on a separate sheet would be nice too.

Thanks again to both of you.

Heather_Verde

If your formula works as you expect it to on your current sheet, you can maintain the criteria to select on a separate sheet. Just replicate your Boston, Chicago, and Atlanta column headers and the selections to use in row 1 on a separate sheet. Then in the formula, replace the first portion with the range from the other sheet (you can use the Reference Another Sheet link when entering the range and navigate to the reference sheet and select the exact cells as you would have in row 1. So your formula would look something like this:

=JOIN(COLLECT({Separate Sheet Range},[FIRST COLUMN]@row:[SECOND COLUMN]@row,<>""),CHAR(10))

Then you can delete row 1 on your current sheet and allow the data shuttle to process.