Modifying Validations Using Indirect Formulas

[Rick G]

I've created some "conditional" validation drop-downs that use the "indirect" formula.

For the sake of simplicity, let's say the heading in cell A1 is "Produce" and the heading in cell B1 is "Item".

The drop down that appears in cell A2 offers the options of Fruit & Vegetables.

When the "Fruit" option is chosen in cell A2, the choices provided in the drop down of cell B2 are Bananas, Apples and Oranges. Let's say the choice of Apples is made in cell B2.

The difficulty I am having is that when the user changes the "Fruit" option in cell A2 to "Vegetables" or blank, nothing changes in the corresponding B2 cell and the Apples option that was originally chosen remains visible.

Is there a way to write an IF statement in the Validation Source box (assuming that you have allowed the option of "List")?

I have several formulas that are looking at the result in cell B2 to make a determination on what values to return and I want to ensure that the correct values are shown.

Thank you,

Rick

 

[Jerry Sullivan]

Rick,

If I'm understanding you correctly, it sounds like you have been able to get your Validation Dropdown lists for B2 to give the right list when clicked based on the current value of A2; but you want to go one step further and have the value of B2 change if A2 is changed to a different category. Is that right?

I think the only way to do that would be to use VBA Worksheet Event code.
The "Custom" option in DV will allow you to enter formulas to define acceptable DV values for a cell, however that formula doesn't affect existing values in cells.