Restrict input to only values of drop-down list

I have a sheet with a column that I've defined as a Dropdown List with a set of values.

However, it seems like Smartsheet still allows other values to be manually typed into the cell. Is there any way to restrict input to only those values defined in the drop-down list?

Find more posts tagged with

Sheets

Comments

Atus Bartal

Unfortunately, restriction is not possible. It would be a useful enhancement indeed.

JLC

We've run into this before as well - +1 to having this enhancement implemented!

Tim McCarthy

Could a formula be used to verify whether the entry is equal to one of the drop-down choices? And then display a text message, if necessary, that a value from the drop-down needs to be selected?

Joe Varghese

That's so odd .. why would one define a drop-down box other than to restrict input options and make data in that column consistent? Wonder what the thought/motivation was by Smartsheet on allowing the manual entry .. Excel does this under Data Validation

J. Craig Williams

Tim,

Depending on the number of choices, this quickly becomes prohibiitively complicated.

Craig

Travis

Tim, you could do this with conditional formatting, rather than a formula.

When you create a rule for a dropdown list, you have the option to "select all" Dropdown list items, then select "Apply format when condition is NOT met". Now the rule looks for anything that is not one of the predetermined Dropdown list items and can format the cell based on this.

Screen Shot 2016-03-08 at 2.41.05 PM.png

J. Craig Williams

Fantastic! I did not know that and have two instances where it will help.

Thanks Travis!

Craig

Travis

Just keep in mind, this will not automatically include new items you add to the dropdown list. If you add new options, you will need to update the rule.

Atus Bartal

That's fine, Travis, but can it (value restriction) still be an enhancement request? I don't think it's too hard to implement.

Joe Varghese

This may be a newbie question, but conditional formatting applies to the entire column, right?

In our sheet, we have several rows that are intentionally blank. One temporary workaround is to create custom criteria, and in addition to all the possible values of the drop-down list also include blank. Ugly, but think it works.

The initial question remains, though - if a person has created a sheet with a drop-down list, why are items that are not on the list by default not accepted?

The more I'm thinking about it, this seems more like a bug than an enhancement request, but hopefully it can be implemented whichever way it's treated.

Thank you!

Travis

Hi Everyone - The functioanlity you are likely looking for is called data validation. Yes, I will submit this request as an enhancement!

Atus Bartal

Thanks a lot, Travis!

Brad Jones

+1 to this enhancement request.

I'm trying to keep the input within a certain set of fixed responses, and currently can't fix the user

Will try the format thing, but want the "lock dropdown" feature please.

Travis

Thanks Brad! Vote added.

Colton Schweitzer

Hello all,

My name is Colton and I am one of the User Experience Designers here at Smartsheet.

I'm reviving this thread because we are actively working on new data validation features. As we work to improve this experience, we are going to be conducting some Usability Studies from July 5th-July 13th. We expect these sessions to only last about an hour.

If you are interested, please fill out this webform and include 2-3 times between July 5th and July 13th that might work for you.

Cheers,

Colton

https://app.smartsheet.com/b/form?EQBCT=bbccacf667424ef1bbe8f43a5fd02144