Help with Multiple levels of Data Validation

[SustainUSteve]

Hello,

I'm looking to put a drop down list in a column E2:E32 which gives the option to select "White", "Gray", or "Black". I also want a countif to trigger a warning if a duplicate is entered in column E withn columns B through D of the same row.

Is it possible to add two levels of data validation, or a text string within the custom option of data validation that would allow me to do both?

 

[seguin85]

I have an idea, not sure if it would work for you.

	A	B	C	D	E	F	G	H	I
1		Colour1	Colour2	Colour3	Drop-Down		White	Grey	Black
2
3
4

<tbody>
</tbody>

Column E would be your drop down, but the range would be G2:I2.

In G2 use formula =IF(COUNTIF($B5:$D5,"White")=0,"White","")
In H2 use formula =IF(COUNTIF($B5:$D5,"Grey")=0,"Grey","")
In I2 use formula =IF(COUNTIF($B5:$D5,"Black")=0,"Black","")

The drop down would only show values that are not in columns B:D.

 

[Eric W]

Welcome to the forum.

I like sequin's idea. You can hide the helper columns G:I if you want.

The other possibility I can think of is to use this custom DV formula:

=ISNUMBER(SEARCH("|"&E1&"|","|White|Gray|Black|"))*(COUNTIF(B1:D1,E1)=0)

It handles both of your conditions without needing helper columns, but it doesn't have a drop-down list either.

 

[seguin85]

Correct me if I am wrong but would the custom DV work:

=AND(E2<>B2,E2<>C2,E2<>D2)

Again it wouldn't have a drop down.

Again wouldn't have a drop down

 

[Eric W]

That would work to prevent the duplicates, but would not restrict the input options to White, Gray, or Black.