Reset cell value on list change

[AdRock]

I am having a problem with validated lists

In A1 I have a drop down with values "Large" and "Small"

In A2 I have a drop down depending on what was selected in A1

If A1 is selected, there is a list of numbers 1 - 10, if A2 is selected, there is a list of numbers 1 - 3

What i need to do is, if in A1 i have "Large" and in A2 i have 10 and I want to change A1 to "Small, i need to reset the list in A2.

At the moment if i change A1 to "Small", the list changes but the value still stays the same.

 

[Jerry Sullivan]

You could use a Worksheet_Change event....

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If Range("A1") = "Small" And _
            Range("A2") > 3 Then
            Application.EnableEvents = False
            Range("A2") = 1 'reset default value
            Application.EnableEvents = True
        End If
    End If
End Sub

 

[jbeaucaire]

If you don't want to use VBA, you can use a conditional formatting trick to HIDE the current value of the second cell if it no longer matches to your now-changed first cell.

Assuming you are using named ranges for your dependent drop downs....something like:

Named Range LARGE refers to: Z1:Z10 (where your numbers 1-10 are listed)
Named Range SMALL refers to: Z1:Z3 (the number 1-3 from the list above)

The Data Validation in cell A1 is Allow: List - Source: Large,Small
The Data Validation in cell A2 is Allow: List - Source: =INDIRECT(A1)

Now you're back to your working cells A1 and A2. Now we will create a conditional formatting formula on A2.

Select A2
Apply the Conditional Formatting settings of:

Condition1: Formula Is: =NOT(ISNUMBER(MATCH(A2,INDIRECT(A1)))
Format... Font: WHITE COLOR

The point here is if you select LARGE-8, then change to SMALL... the 8 is not in the SMALL list so the font will turn white to hide the current answer. It will LOOK like it vanished, so the user will select the drop down again to choose a value 1-3.


BTW, final tip. I try to stay away from names and codes that match Excel Functions. Since LARGE and SMALL are also Excel formula functions, I would consider switching to LRG and SML.