Data Validation Function With An If Statement (2007)

Topic

New Reply

Is it possible to use the Data Validation function with an if statement; then if the if statement is false limit the input value based on a drop down list?

Example:

A1 can have any value

B1 is where I want the Data Validation with an if statement to look at A1:

If A1 has a particular value (e.g., APPLE), then I would like B1 populated with APPLE; however, if A1 does not equal APPLE, then I would like B1 limited to three values the user can select from a drop down menu (e.g., RED, BLUE, or GREEN).

Any ideas would be greatly appreciated.

Reply | Quote

Replies

Why not put the datavalidation in C1 for Red, Blue, green, then in B1 enter the formula:

=IF(A1=”APPLE”,A1,C1)

So you enter into A1 or C1 and B1 selects the appropriate one…

Steve

Reply | Quote

You can use a range which contains a list that you validate against, and that range can contain a formula that varies depending on the value in A1.

For example the validation list for B1 could be G1:I1 and those cells can contain…

	G	H	I
1	=IF(A1=”Apple”, “Apple”, “Red”)	=IF(A1=”Apple”, “”, “Green”)	=IF(A1=”Apple”, “”, “Blue”)

This could be a bit of a pain if you have many rows, but you just need to copy the formula down and then hide these columns.

StuartR

Reply | Quote