Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Establishing a default on dependent data validation lists
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Establishing a default on dependent data validation lists
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Establishing a default on dependent data validation lists
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("D16:D20").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" wrote: 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. -- Cathy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Establishing a default on dependent data validation lists
Matthew:
Thank you for your help. -- Cathy "Matthew Pfluger" wrote: 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("D16:D20").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" wrote: 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. -- Cathy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation-Dependent Lists | Excel Discussion (Misc queries) | |||
Data validation dependent lists | Excel Discussion (Misc queries) | |||
dependent lists on data validation | Excel Discussion (Misc queries) | |||
Data Validation and Dependent Lists Q | Excel Worksheet Functions | |||
Data Validation & Dependent Lists | Excel Worksheet Functions |