Dependent Drop-down Lists and Conditional Formatting

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.