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.
Bookmarks