Can anyone give me insight into how I might be able to get Excel to
recalc or update a validation list that is dependant on a different
one? Currently, I have a primary list and a secondary list. The
secondary is using the indirect function to refer to the primary. The
problem is that when the primary is changed, the secondary stays on an
option that is not current until you actually click on the dropdown,
at which point it only shows options from the updated primary. I
would at least like to be able to write a script or modify a function
so that the secondary blanks out if the primary is changed at any
point. I've tried writing a UDF using a case select, which
appropriately updates when the dependant cell changes, but the data
validation allow formula gives me a "A named range you specified does
not exist" error. Any suggestions?

Regards,
Brian

You could use a macro so that each time the "parent" cell changes, the child
cells gets reset to "".

If you want to try, right click on the worksheet tab that should have this
behavior. Select view code and paste this code in the code window.

Change the range to what you need (I used A1). And I cleared .offset(0,1) (B1).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
Target.Offset(0, 1).Value = ""
Application.EnableEvents = True
End Sub



wrote:

Can anyone give me insight into how I might be able to get Excel to
recalc or update a validation list that is dependant on a different
one? Currently, I have a primary list and a secondary list. The
secondary is using the indirect function to refer to the primary. The
problem is that when the primary is changed, the secondary stays on an
option that is not current until you actually click on the dropdown,
at which point it only shows options from the updated primary. I
would at least like to be able to write a script or modify a function
so that the secondary blanks out if the primary is changed at any
point. I've tried writing a UDF using a case select, which
appropriately updates when the dependant cell changes, but the data
validation allow formula gives me a "A named range you specified does
not exist" error. Any suggestions?

Regards,
Brian

--

Dave Peterson

I have actually tried this. I believe that the problem with this is
that excel does not see a change in a validation list box as a cell
value change. There is also a way to do the same thing whenever the
validation box is clicked, but I dont want the values resetting
everytime one of these parent lists (there are about 20 of them) is
clicked, only when one is changed.