Displying Blank Cell in Dependent List

Dear All,

I currently created a dependent list in excel, using data validation. There are three columns that are depedent on each other (state, division, and branch location). In A2 you select a state from the dropdown menu. Based on this result, you then select in B2 which company division you want in that particualr state from another dropdown. Lastly, in C2 you select the address of the division based on the results of B2.

My problem arises when you change the information in A2. For example, if the orignially inputted state in A2 was California, B2 and C2 would display the correct information. However, if you were to change the information in A2 to Arizona, the information displayed in B2 and C2 is still from California.

How can I make those cells be blank after A2 is changed?

Sajan

One way is to use conditional formatting in B2, such that if B2 is not one of the allowed values depending on the choice in A2 then it gets highlighted - the formatting could be white on white so that it would appear blank, but personally I would prefer to change the background to red, say, so that it stands out as needing to be changed. The same approach could be taken with C2.

It should be noted though that other formulae in the workbook which use the values in A2:C2 might return errors if the values are invalid, and so you may need to adjust those to show blanks (e.g. using IFERROR).

Hope this helps.

Pete

I never thought of this solution. I will try it out, thanks Pete!