Data Validation / Restrictions

**naiku** · 08-11-2014, 04:05 PM

I am having some difficulty with data validation and not sure how best to approach the problem. On my spreadsheet I have a drop down list with 5 selections, depending on what is picked from this list will either set a default value to another field or present a list of options to choose from. For example:

If I select 1 in column A, I want column B to display a default value of "hour" and not allow it to be changed.
If I select 2 in column A, I want column B to allow me to either pick minute or second, but only pick one of those 2 values
And so on for values 3, 4 and 5 in column A.

I have tried using an IF formula, but this will only work for single values (unless I am missing something) so will not force a user to pick one of 2 values. I have tried looking at Data Validation but cannot figure out how to get it to either populate a field with a default value (see "hour" above) or restrict the input to a list of values.

Any suggestions? I am not opposed to using VBA, but do not want to have to force end users into running macro's.

Thanks.

**Ace_XL** · 08-11-2014, 04:09 PM

You cannot have a value populated in a cell and have the same as a blank cell available for input based on data validation at the same time without VBA.

Having said that, you'd probably be best placed using dependent data validation lists with some validation (i.e. 1 in your case) having a single value to choose from

have a look at dependent data validation techniques here

http://www.contextures.com/xlDataVal02.html

**naiku** · 08-11-2014, 04:20 PM

Originally Posted by Ace_XL

have a look at dependent data validation techniques here

http://www.contextures.com/xlDataVal02.html

Funny, I have that exact same link open in another tab. Reading through it now, thanks.

Edit: Hmmm not sure that the link will work, I have multiple columns that need to be restricted based on the first column, the problem is that I can't use the name more than once. The INDIRECT formula to populate column B with the correct list, would not then work for column C as it contains different data. I guess I could combine A and B into a unique name for column C, but that would get pretty cumbersome by the time I got to column K.