Need data val. dependent dropdowns that recalculate/reset when initial dropdown changes.

[jasondb]

Hello,

I've made simple dependent dropdowns using data validation and named ranges.

The problem I have is that once the dependent dropdown is chosen, it is possible to then go back to the first dropdown and choose whatever you want, leaving the dependent dropdown unchanged.

For example, in the attached workbook, one can choose 'Berries' as a fruit, which will allow them to choose 'Black' as a colour. If they were then to change the fruit to 'Bananas', I would need the colour to not remain as 'Black'.

Is there a solution (not using developer tools)?

Jason

[kev_]

This does not prevent it from happening, but the user is made aware.

One option
- warn the user that the combination is invalid with conditional formatting in either (or both cells)
- conditional formatting formula:
=COUNTIF(INDIRECT($A$11),$B$11)=0

Another option
- warn the user with a formula in an adjacent cell:
=IF(COUNTIF(INDIRECT(A11),B11)=0,"INVALID","")

[kev_]

Again - this does not prevent the invalid combination, but it's a different way to handle it.


Use data validation on another cell to prevent the user moving forward
In cell A13 data validation formula
=COUNTIF(INDIRECT(A11),B11)=1

See attached workbook (which also contains both post#2 options)
- any value can be placed in A13 but ONLY if there is a valid combination in A11/B11

.
Combo Invalid.jpg

[kev_]

You asked for a non-VBA solution but others may be interested in a simple option

This clears the values in both cells when A11 is selected

Place in sheet module:

Please Login or Register  to view this content.