Dependent Drop-down Lists and Conditional Formatting

Hi Guys,

Can't get my head round this one.

I have three drop-down boxes in 3 adjacent columns. Column 1 is free-choice, Columns 2 and 3 drop-downs are variable dependent on what is in Column 1. That works fine.

I have an issue with over-type but I can solve that with protection. Again fine.

BUT:
1. How do I make the user choose something i.e. not just leave the cell in column 2 or 3 blank by ignoring it (i.e. blank is an error but only after drop-down in column 1 is activated)
2. Also, if the user has completed the line (columns 1,2 and 3) and then changes column 1, columns 2 and 3 are now reading from the incorrect drop-down boxes (i.e. they are now in error but this is accepted and not flagged).

Cheers in advance.

Originally Posted by FDibbins

Hi and welcome to the forum

For DD 2 and 3, in CF, use something like....

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =ISERROR(VLOOKUP(A1,J1:J6,1,0))

where A1 is 1 of the DD's and J1:J6 is the range that contains the DD values

-------------------------------------------------------------------------------------------------

I modified your formula to create conditional formatting to ensure the existing/left-over value in a dependent, cascading drop down cell (E2) is valid after the controlling cell value (A2) changes.
The formula I used must be dynamic. It refers to a Named Range by concatenating: "CUSTNAMES_"&$A$2.
The Conditional Formatting worked in E2 when I originally set it up, but when I tried to replicate it in another cell (E6), the new formula failed.... and, upon checking, the original CF I created in E2 failed too.

The formulas I used to create the CF are below. A2 is a controlling Drop Down dictating the Data Validation lists in E2, E6 and several other locations in the worksheet.

CF applied to cell E2: =ISERROR(VLOOKUP(E2,"CUSTNAMES_"&$A$2,1,0))
CF applied to cell E6: =ISERROR(VLOOKUP(E6,"EECLASS_"&$A$2,1,0))

I tried creating the formula in a regular cell to troubleshoot. The formula only works when I spell out the named range but not when I concatenate.
I am not sure if it matters, but I did use the same concatenate approach to create the INDIRECT cascading drop down lists in the cells that need the Conditional Formatting. Not sure why concatenating works in the Data Validation but not in Conditional Formatting.

Thanks for any help and suggestions you might be able to send my way.

Perfect.

Thanks!