data validation defaults

[cathy]

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.

 

[Otto Moehrbach]

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]

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.