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" wrote:
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.
--
--
Cathy