Establishing a default on dependent data validation lists

[cathy]

I have a pricing model that has dependent data validation drop down lists set
up. When one item is selected in the first data - I want the second data
validation to change its default (already have it changing the options
available to select). For example, when someone selects "promotion" from DV
list 1, the discount options in DV list 2 are 0% - 20%. Say the user
selected 20% but then went back up to DV list 1 and changed it from
"promotion" to client where the list is 0% - 10%. While the options are
there - list 2 doesn't automatically change it's default - it remains at 20%
until manually changed.

Below will clarify a little further. Hopefully someone can assist. Thanks.

Column B, rows 19 - 57 each list a product name. These are not dv cells -
simply names.

Column D has a formula with pricing information based on the variables that
are selected. The price remains hidden unless the product is selected in the
proposal. When the product is selected, the price appears. I do not require
assistance with column D.

Column E, rows 19 - 57 are each set up with dv cells, allowing the user to
either select the product (option "1") or not select the product (option
"0"). This works perfectly when cell D15 is "Client" and "Promotion."

However, when D15 has "Affiliate" selected, there are certain products that
are bundle priced for Affiliates. So, by nature of select "Affiliate" these
5 product rows should have the respect E cell default to 1. I don't want the
sales rep to have to go in and select Affiliate and then manually select
option "1" for products that are bundled.

The way it is set up now everything works - but there are numerous steps for
my sales reps. The sales rep current selects Affiliate and then say, "ok,
now I have to change the data validation drop downs for all 5 products that
are part of the affiliate bundle to option 1." Instead, I'd like to rep to
select Affiliate and all 5 products automatically convert to option 1.

Similiar, with cell E72. The minute D15 is selected as Affiliate, I want it
to default to 0% until the rep alters it. The idea is to minimize any
oversights. The data validation currently works - it's just that if someone
changes D15, all the other adjustments have to be manual as opposed to
automatic.

 

[Matthew Pfluger]

You need to use a Worksheet_Change event to detect changes to D15. In the
Project Explorer of the VB editor, double click on the worksheet that this
data is in and enter this macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$15" Then
Application.EnableEvents = False
Range( *** put the range of the products to change here *** ).value =
Range("E72").value = 0
Application.EnableEvents = True
End if
End Sub

If this doesn't work for you, there's another method to try, but it's a
little more complex. Let me know if this helps.

Matthew Pfluger

 

[cathy]

Matthew:

Thank you. I'm a little unclear where you have "put the range of the
products to change here." The data validation list for cell E 72 comes from
data entered in cells E545 to E565 (0% - 20%). Am I to put E545:E565?

Whenever there is a change to cell D15 - it should default to 0.

Also, for cells E19, E25, E26, E28 and E52 - these should default to "1"
when "Affiliate is selected." Is that included in the programming code?

thanks for your assistance.

 

[Matthew Pfluger]

Cathy,

You don't have to put in the range of the data validation, only the
addresses of the cells you want to change. Their data validation will remain
intact. Thus, when D15 changes and you want the products in cells D16 - D20
to change (just as an example), you would put:

If Target.Address = "$D$15" Then
Application.EnableEvents = False
Range("D1620").value = 0
Range("E72").value = 0
Range("E19,E25,E26,E28,E52").value = 1
Application.EnableEvents = True
End if

I believe you have products in cells E19 - E57 that you want to change if
"Affiliate" is selected. Then, you should include a line like:
Range("E19:E57").value = 1
Just change the "E19:E57" part to the range of cells you want to change.

Does this help?
Matthew Pfluger

 

[cathy]

Matthew:

Thank you for your help.

--
Cathy

Cathy,

You don't have to put in the range of the data validation, only the
addresses of the cells you want to change. Their data validation will remain
intact. Thus, when D15 changes and you want the products in cells D16 - D20
to change (just as an example), you would put:

If Target.Address = "$D$15" Then
Application.EnableEvents = False
Range("D1620").value = 0
Range("E72").value = 0
Range("E19,E25,E26,E28,E52").value = 1
Application.EnableEvents = True
End if

I believe you have products in cells E19 - E57 that you want to change if
"Affiliate" is selected. Then, you should include a line like:
Range("E19:E57").value = 1
Just change the "E19:E57" part to the range of cells you want to change.

Does this help?
Matthew Pfluger