CODEWITHSUNDEEP
excelexcel-formulaexcel-2007vba
C.Harlie
C.Harlie

Reputation: 41

Highlight with data validation and conditional formatting, but with optional data validation fields

I want to build a search function into my spreadsheet. Because my data is formatted in a way such that Excel filter will not work (won't filter by columns), I am using data validation and conditional formatting to highlight and sort.

My spreadsheet currently has 3 lists data validation fields with the options "Yes" and "No". Each of the 3 fields is compared to different cells that will determine the highlighting according to =AND($K$2=D$35,$L$2=D$63,$M$2=D$82). K2, L2, and M2 are my 3 data validation fields and D35, D63, and D82 are what's being compared with the data validation to determine highlighting.

This currently works, but I must pick "Yes" or "No" for all the data validation fields and it will highlight the cell only if it has the exact the combination of choices.

How can I make this so I don't have to pick a choice for all data validation fields. For example if I choose "Yes" for the first 2 fields and leave the third blank or something, it will only highlight based on what I've selected only.

Upvotes: 3

Views: 221

Answers (1)

C.Harlie
C.Harlie

Reputation: 41

=AND(COUNTA($K$2:$M$2)>0,COUNTA($K$2:$M$2)=($K$2=D$35)+($L$2=D$63)+($M$2=D$82))

This formula goes into conditional formatting.

Solution came from Marcelo Branco on mrexcel.com

Upvotes: 1

Related Questions