Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
data validation defaults
I have a pricing proposal model. In it is a data validation drop down menu
in cell $D$15 that allows the user to select Affiliate, Client, or Promotion. The product names are listed in column B, Column D contains the price for the product and Column E allows the user to select the product with a data validation list of 0 or 1. There are certain products that are automatically included when Affiliate is selected in $D$15. I want to set it up so that when $D$15 equals Affiliate, these line items (in column E's drop down list) default to 1 instead of 0. I established a named list which allows 1 as the only option, but it doesn't default to 1 - it's still a manual process. Also, in cell $E$72 is a discount percentage data validation drop down list. It is set up in such a way that when Affiliate is selected in$D$15, only 0% appears in the drop down, when Client is selected, 0% - 10% appears in the drop down and when Promotion is selected, 0% - 20% appears. However, it does not automatically default. So, if I previously had Promotion at 20% and switched to Affiliate, the 20% would be the default in cell $E$72. -- Cathy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
data validation defaults
Cathy
Please understand that you are the only one who knows your operation and how it works. It is difficult for someone outside of your operation to know what you have and what you want because you are using terminology that is peculiar to your operation. Let me state what I think you mean and you tell me if I'm right. I'll refer to a Data Validation cell as a DV cell. Cell D15 is easy to understand. Column B is not. Do you mean that Column B has DV cells with the list of products? I don't know if Column D has DV cells or not nor if you want help with it. Column E. You say Column E has DV cells. You also say that, if Affiliate is chosen in D15, you want the Column E DV cells to display (as the only option) a 1. I gather that you want Column B to display a 1 and a 0 so that the user can choose one or the other if anything other than Affiliate is chosen in D15. I don't understand where "There are certain products that are automatically included when Affiliate is selected in $D$15" comes into all this. Do you mean that you want the DV lists in Column B to include only certain products when Affiliate is chosen in D15? What about when the other choices are made in D15? I understand what you want with E72, but you say that you set it up to do all that and it doesn't do it. How did you setup that DV? HTH Otto "cathy" wrote in message ... I have a pricing proposal model. In it is a data validation drop down menu in cell $D$15 that allows the user to select Affiliate, Client, or Promotion. The product names are listed in column B, Column D contains the price for the product and Column E allows the user to select the product with a data validation list of 0 or 1. There are certain products that are automatically included when Affiliate is selected in $D$15. I want to set it up so that when $D$15 equals Affiliate, these line items (in column E's drop down list) default to 1 instead of 0. I established a named list which allows 1 as the only option, but it doesn't default to 1 - it's still a manual process. Also, in cell $E$72 is a discount percentage data validation drop down list. It is set up in such a way that when Affiliate is selected in$D$15, only 0% appears in the drop down, when Client is selected, 0% - 10% appears in the drop down and when Promotion is selected, 0% - 20% appears. However, it does not automatically default. So, if I previously had Promotion at 20% and switched to Affiliate, the 20% would be the default in cell $E$72. -- Cathy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
data validation defaults
Otto:
Thank you for getting back to me. Hopefully this will clarify. 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 "Otto Moehrbach" wrote: Cathy Please understand that you are the only one who knows your operation and how it works. It is difficult for someone outside of your operation to know what you have and what you want because you are using terminology that is peculiar to your operation. Let me state what I think you mean and you tell me if I'm right. I'll refer to a Data Validation cell as a DV cell. Cell D15 is easy to understand. Column B is not. Do you mean that Column B has DV cells with the list of products? I don't know if Column D has DV cells or not nor if you want help with it. Column E. You say Column E has DV cells. You also say that, if Affiliate is chosen in D15, you want the Column E DV cells to display (as the only option) a 1. I gather that you want Column B to display a 1 and a 0 so that the user can choose one or the other if anything other than Affiliate is chosen in D15. I don't understand where "There are certain products that are automatically included when Affiliate is selected in $D$15" comes into all this. Do you mean that you want the DV lists in Column B to include only certain products when Affiliate is chosen in D15? What about when the other choices are made in D15? I understand what you want with E72, but you say that you set it up to do all that and it doesn't do it. How did you setup that DV? HTH Otto "cathy" wrote in message ... I have a pricing proposal model. In it is a data validation drop down menu in cell $D$15 that allows the user to select Affiliate, Client, or Promotion. The product names are listed in column B, Column D contains the price for the product and Column E allows the user to select the product with a data validation list of 0 or 1. There are certain products that are automatically included when Affiliate is selected in $D$15. I want to set it up so that when $D$15 equals Affiliate, these line items (in column E's drop down list) default to 1 instead of 0. I established a named list which allows 1 as the only option, but it doesn't default to 1 - it's still a manual process. Also, in cell $E$72 is a discount percentage data validation drop down list. It is set up in such a way that when Affiliate is selected in$D$15, only 0% appears in the drop down, when Client is selected, 0% - 10% appears in the drop down and when Promotion is selected, 0% - 20% appears. However, it does not automatically default. So, if I previously had Promotion at 20% and switched to Affiliate, the 20% would be the default in cell $E$72. -- Cathy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom data validation on cells with data validation values | Excel Worksheet Functions | |||
Are there defaults for data fields in pivot tables (sum or count) | Excel Worksheet Functions | |||
pivot table data field defaults to COUNT, would like it to default toSUM | Excel Discussion (Misc queries) | |||
data sort defaults | Excel Discussion (Misc queries) | |||
setting defaults for data point size, color, shadow, etc. | Charts and Charting in Excel |