delete value of dependant list automatically when "mother-list" gets manually deleted.

Hi,

Iīm currently working with 3 dropdown lists depending on each other using data validation and =indirect(). I arranged them like in a top-down approach where you have a mother-list and child-lists (categories-->subCategories-->subCategoryCriteria). The user should easily chose first a category then a subCategory etc... This part works perfectly

My question is: when a user chose in the dropdown list e.g. a category, a subCategory and a subCategoryCriteria and he now maually deletes the value in the highest level, which is the Category (mother-list), all sub level values like subCategory and subCategoryCriteria will stay. It shouldnīt be that way, because itīs not logical. If you turn a tree upside-down (top-down approach) and cut one of the higher positioned trunks, all lower positioned trunks will fall off as well, right? So, my question is: can I use a conditional formatting or a formular to delete or fade out the value in the lower lists when the higher list value is being deleted?

I tried to use If-formulars in combination with indirect in data validation, like =IF(A1="";("");INDIRECT(B1))...just and example.

Would be great if there would be a solution without using VB. As I said before - the value doesnt need to be deleted, it could also be automatically face out like white on white or something. Itīs just confusing for the user if he deletes the highest choice and the lower choices still stay.

You know, K.I.S.S. - keep it simple stupid ;-)

Thanks in advance,
C

Attached youīll find a small cut-out of the workbook. Please note that the third and lowest criteria "metric" has not been completely filled yet. Only the first 2 categories will return any values there.

TEST.xlsx

Itīs actually working now, thanks. BUT: letīs pretend someone is first chosing all criterias and then deletes the highest criteria which is category. The lower criterias will fade out since we applied CF, but if he choses another category now, the old criterias will fade in again instead of staying faded or just blank.

Anybody else an idea how to fix this?