Combining a data validation list with a custom formula (including error message)

Hi,

I have problem that I can’t solve.

I want to combine a data validation list with a custom formula (including error message).

In the cells e3:e5 I want to have drop down list with the choice “low”, “medium” and “high” but you should only be able to select a value (“low”, “medium” and “high”) once. That is to say, you have chosen value “low” in the first cell, you should receive an error message, that says something about only unique values are allowed, if you choose low again.

Thanks in advance

Originally Posted by bebo021999

Try:
Validation in E3:
Allow: Custom
Formula:=OR(E3="low",E3="medium",E3="high")
In E4:
Formula:=AND(OR(E4="medium",E4="high",E4="low"),E4<>E3)
In E5:
Formula:=AND(OR(E5="low",E5="medium",E5="high"),E5<>E3,E5<>E4)

Hi again,

thanks for the proposal, but it will not solve the problem as I understand it, because I also want to have a drop down list.

Originally Posted by popipipo

Or take a look at this

Thanks for a good proposal, but it will not solve the problem fully. Since it should always be possible to select all options but it should be an error message if you choose an option that is already selected.

Originally Posted by Tony Valko

Try this...

Enter the selections in a range of cells, say Z2:Z4


Enter this array formula** in AA2 and copy down to AA4:

=IFERROR(INDEX(Z:Z,SMALL(IF(ISNA(MATCH(Z$2:Z$4,E$3:E$5,0)),ROW(Z$2:Z$4)),ROWS(AA$2:AA2))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Now, setup the drop down list...

Select the range E3:E5
Data Validation
Allow: List
Source: =OFFSET(AA$2,,,COUNTIF(AA$2:AA$4,"?*"))
OK out

This will restrict the selections in the drop down list so that once a selection is made that selection will be removed from the list and no duplicates can be selected.

Thanks for a good proposal, but it will not solve the problem fully. Since it should always be possible to select all options but it should be an error message if you choose an option that is already selected.

Originally Posted by ChemistB

I think the above solutions are the only ones available to you unless you want to resort to a VBA solution and that would only work if the user enables macros.

I see. A VBA solution would be great, could anyone help me with that?