Conditional formatting based on dependant lists

I have the following scenario:

General info
Workbook with two worksheets:
- Transactions
- Lists

Transacciones is the main worksheet, Lists only has all the lists (named ranges) I use in Transactiones.

Lists:
Name: AccountClasses
Range: =OFFSET(Lists!$A$2,0,0,(COUNTA(Lists!$A:$A)-1),1)

Name: EgressCol
Range: =Lists!$C:$C

List AccountClasses contains the following values: Ingress, Egress, Transfer.

Data Validations in the Transactions worksheet:
Col D: = AccountClasses
Col E: =OFFSET(INDIRECT($D2),0,0,(COUNTA(INDIRECT(D2&"Col"))-1),1)

Registering a new transaction
In the drop-down menu in col D I select the value “Egress”, then the drop-down list in col E is populated with the values of EgressCol. Now I select a value, lets say “Cash payment”.

If at a later time I change the value of col D, lets say to “Ingress”, then “Cash payment” in col E doesn’t match the values available for IngressCol (the correspondent dynamic list for the Ingress value).

I need a conditional formatting formula that checks whether the value in col E is listed in the corresponding list of the value selected in col D.

Request
I found this formula that works fine, but it gets Excel to work really slow.

=COUNTIF(INDIRECT(SUBSTITUTE($D2," ","")&"Col"),$E2)=0

I’ve been trying some other methods but failed. Any help would be really appreciated.

All of your functions are volatile, which is going to make the workbook run slow in there are a lot of cells dependent on them. Conditional formatting is always volatile, but your list definitions need not be; you should define them using INDEX rather than OFFSET.

That said, if A1 contains a range name (maybe from a drop-down), and B1 is an entry (also maybe from a drop-down), then you can highlight invalid entries with a CF formula like

=ISNA(MATCH(B1, INDIRECT(A1), 0))

You’re welcome. Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes