Highlight Cells Outside of Data Validation List

[sarahmoa]

Hi! I have a spreadsheet with a Data Validation column referencing a range on a separate sheet. What I'd like to happen is to highlight any cells not meeting that criteria. I know it throws the Data Validation error, but I'm having trouble getting people to understand I can't work with data outside of the list, but I don't want to lock them down from free-texting either. I'd like to highlight the cell to give them a visual queue that more information is needed.

I'm familiar with Conditional Formatting, but I haven't found a way to do this without creating a separate rule for each item in my list (20 items so far).

 

[Fluff]

Assuming the DV is in A2 downwards & the list is on a sheet called list in A2:A100 try
=ISNA(MATCH(A2,List!$A$2:$A$100,0))

 

[sarahmoa]

That works great! I modified it to fit my spreadsheet with: =ISNA(MATCH(D11,Data!$A$1:$A$20,0))

Is there a way to make it ignore blanks also, or am I reaching? :P

 

[Fluff]

How about
=AND(D11<>"",ISNA(MATCH(D11,Data!$A$1:$A$20,0)))

 

[sarahmoa]

You are a freaking genius, thank you so much!!!

 

[Fluff]

You're welcome & thanks for the feedback