Auto Insert Data Validation Based On Cell Data

[DKTRL]

Hi,

I have create a Order Form and need some help. this order form is for customer to choose the items that they are going to buy. and for certain items we giving them 1 or 2 free gift but not for all.

MySheets1 ("A19") is for choosing the products "Data Validation List"

What i want is that, base on difference products that the customer select in ("A19") i want Cell A25 or A25 & A26 to add data validation list from Sheets2

if Sheets1 ("A19") select = "Products 1" then Sheets1 ("A25") automatically add data validation list from sheets2 A1:A20

if Sheets1 ("A19") select = "Products 2" then Sheets1 ("A25") & ("A26") automatically add data validation list from sheets2 B1:B20 & C1:C20

if Sheets1 ("A19") select = "Products 3" then Sheets1 ("A25") No data validation added

if Sheets1 ("A19") select = "" then Sheets1 ("A25") No data validation added



Any Help Will Be Appreciate.

 

[mumps]

Hi DKTRL. The additional information helps a bit but there is still some confusion. Below, I have copied your description and have inserted some questions in relation to each stage. 1st Stage
1) The customer has to select an items in the list. Here we refer to A19 "In total there are about 20 difference items" --- Can the customer choose any of the 20 items?
2) If the selected items is a promotion item with 1 free gift, then A25 will add a "Data Validations List" for customer to choose. --- How does the customer know if the item is a promotion item with 1 free gift instead of 2 free gifts? What is the source of the validation list that would go into cell A25?
3) If the selected items is a promotion item with 2 free gift then A25 and A26 will add "Data Validations List" for customer to choose for 2 free gift item. --- How does the customer know if the item is a promotion item with 2 free gift instead of 1free gifts? What is the source of the validation list that would go into cell A26?
2nd Stage
1) If customer has completed 1st stage selection in A19, the order form will than automatically add another selection list in A20 "CLICK HERE FOR SELECT" for customer to select. --- Is "CLICK HERE FOR SELECT" the only choice in the list in A20 or are there other items to choose from? If there are other items, what are they? If there arej
2) If customer has do he selection in A20, then i want A19 to be Locked to prevent further change. cause all the information in this order form will be transfer to a Userform for further detail add. for example shipping cost. etc. than Userform will update data to the sales directory workbook. if the products selection is not in sequence. if customer after select products in A20 and than delete data in A19 than there will be a error. --- If A19 is locked, when and how do you want it unlocked?
3) If the selected items is a promotion item with 1 free gift then A27 will add a "Data Validations List" for customer to choose. --- How does the customer know if the item is a promotion item with 1 free gift instead of 2 free gifts? What is the source of the validation list that would go into cell A27?
4) If the selected items is a promotion item with 2 free gift then A27 and A28 will add "Data Validations List" for customer to choose for 2 free gift item. --- Ho w does the customer know if the item is a promotion item with 2 free gift instead of 1 free gifts? What is the source of the validation list that would go into cell A28?
3th Stage (Final Stage)
1) If customer has completed 2nd stage selection in A20, the order form will than automatically add another selection list in A21 "CLICK HERE FOR SELECT" for customer to select. --- Is "CLICK HERE FOR SELECT" the only choice in the list in A21 or are there other items to choose from? If there are other items, what are they?
2) If customer has do he selection in A21, then i want A20 to be Locked to prevent further change. --- If A20 is locked, when and how do you want it unlocked?
3) If the selected items is a promotion item with 1 free gift then A27 will add a "Data Validations List" for customer to choose. --- A27 already has a validation list created in Stage 2. Do you want a new list added or do you want to keep the one that is already there?
4) If the selected items is a promotion item with 2 free gift then A27 and A28 will add "Data Validations List" for customer to choose for 2 free gift item. --- A28 already has a validation list created in Stage 2. Do you want a new list added or do you want to keep the one that is already there?
Share The Same Data Validation List.
1) A20 & A21 = A19
2) A27 & A29 = A25
3) A28 & A30 = A26 Your situation is rather complicated. As you can see from my questions, detailed information is needed to make this work.

 

[DKTRL]

Dear mumps

Thanks for your fast respond. please read my answer beside your question.

Hi DKTRL. The additional information helps a bit but there is still some confusion. Below, I have copied your description and have inserted some questions in relation to each stage. 1st Stage
1) The customer has to select an items in the list. Here we refer to A19 "In total there are about 20 difference items" --- Can the customer choose any of the 20 items? / Yes either 1 within the list

2) If the selected items is a promotion item with 1 free gift, then A25 will add a "Data Validations List" for customer to choose. --- How does the customer know if the item is a promotion item with 1 free gift instead of 2 free gifts? In the order form we have some highlight to the customer to be inform which items is under promotion and with free gift. What is the source of the validation list that would go into cell A25? / Sheet2 A1:A20

3) If the selected items is a promotion item with 2 free gift then A25 and A26 will add "Data Validations List" for customer to choose for 2 free gift item. --- How does the customer know if the item is a promotion item with 2 free gift instead of 1free gifts? / In the order form we have some highlight to the customer to be inform which items is under promotion and with free gift. What is the source of the validation list that would go into cell A26? / A25 = Sheet2 A1:A20 & A26 = Sheet2 B1:B20



2nd Stage
1) If customer has completed 1st stage selection in A19, the order form will than automatically add another selection list in A20 "CLICK HERE FOR SELECT" for customer to select. --- Is "CLICK HERE FOR SELECT" the only choice in the list in A20 or are there other items to choose from? If there are other items, what are they? If there are / Use the same products list as A19

2) If customer has do he selection in A20, then i want A19 to be Locked to prevent further change. cause all the information in this order form will be transfer to a Userform for further detail add. for example shipping cost. etc. than Userform will update data to the sales directory workbook. if the products selection is not in sequence. if customer after select products in A20 and than delete data in A19 than there will be a error. --- If A19 is locked, when and how do you want it unlocked / either a reset button or A20 has to select "CLICK HERE FOR SELECT" to unlock A19

3) If the selected items is a promotion item with 1 free gift then A27 will add a "Data Validations List" for customer to choose. --- How does the customer know if the item is a promotion item with 1 free gift instead of 2 free gifts? / In the order form we have some highlight to the customer to be inform which items is under promotion and with free gift. What is the source of the validation list that would go into cell A27? / A27 = Sheet2 A1:A20 is the same list as A25

4) If the selected items is a promotion item with 2 free gift then A27 and A28 will add "Data Validations List" for customer to choose for 2 free gift item. --- How does the customer know if the item is a promotion item with 1 free gift instead of 2 free gifts? / In the order form we have some highlight to the customer to be inform which items is under promotion and with free gift. What is the source of the validation list that would go into cell A28? / A26 = Sheet2 B1:B20 is the same list as A26


3th Stage (Final Stage)
1) If customer has completed 2nd stage selection in A20, the order form will than automatically add another selection list in A21 "CLICK HERE FOR SELECT" for customer to select. --- Is "CLICK HERE FOR SELECT" the only choice in the list in A21 or are there other items to choose from? If there are other items, what are they? / Use the same products list as A19

2) If customer has do he selection in A21, then i want A20 to be Locked to prevent further change. --- If A20 is locked, when and how do you want it unlocked? / either a reset button or A20 has to select "CLICK HERE FOR SELECT" to unlock A19

3) If the selected items is a promotion item with 1 free gift then A27 will add a "Data Validations List" for customer to choose. --- A27 already has a validation list created in Stage 2. Do you want a new list added or do you want to keep the one that is already there? / sorry is a error mistake. is should be add in A29 ans is the same list as A25 & A27

4) If the selected items is a promotion item with 2 free gift then A27 and A28 will add "Data Validations List" for customer to choose for 2 free gift item. --- A28 already has a validation list created in Stage 2. Do you want a new list added or do you want to keep the one that is already there? / sorry is a error mistake. is should be add in A30 ans is the same list as A26 & A28

Share The Same Data Validation List.
1) A20 & A21 = A19
2) A27 & A29 = A25
3) A28 & A30 = A26 Your situation is rather complicated. As you can see from my questions, detailed information is needed to make this work.

I know is complicated, but i hope the my answer above can help you to understand.

Many Many Thanks for your time !

 

[mumps]

OK. We're making some progress. I still don't know what the items in the list in A19 are. Your customers may know whether or not it is a 1 gift or two gift item by looking at the highlight but I have to include this information in the code. So I still need to know all the 20 items in A19 and how I can distinguish by looking at the item if it is a 1 gift or 2 gift item. For example, if an item has a number 1 at the end (Item 1) then I can tell by looking at it that it is a 1 gift item. If an item has a number 2 at the end (Item 2) then I can tell by looking at it that it is a 2 gift item. Could you please list what the 20 items in A19 are so that I can see how to distinguish between a 1 gift and 2 gift item.

 

[DKTRL]

OK. We're making some progress. I still don't know what the items in the list in A19 are. Your customers may know whether or not it is a 1 gift or two gift item by looking at the highlight but I have to include this information in the code. So I still need to know all the 20 items in A19 and how I can distinguish by looking at the item if it is a 1 gift or 2 gift item. For example, if an item has a number 1 at the end (Item 1) then I can tell by looking at it that it is a 1 gift item. If an item has a number 2 at the end (Item 2) then I can tell by looking at it that it is a 2 gift item. Could you please list what the 20 items in A19 are so that I can see how to distinguish between a 1 gift and 2 gift item.

The 20 items is actually cars body parts & cars accessories. and for your convenient maybe we can use the below as the items.

Products 1 to Products 10 = 1 free gift (Free Gift can be choose = FG1 to FG10 )

Products 11 to Products 15 = 2 free gift (Free Gift can be choose = FG1 to FG10 ) + (Free Gift can be choose = FG11 to FG15 )

Products 16 to Products 20 = 0 free gift


Thanks!

 

[mumps]

I have to go out now. I will get back to you as soon as I can.

 

[DKTRL]

I have to go out now. I will get back to you as soon as I can.

Really appreciate for your time.

 

[mumps]

I don't think that this will work. Unless there is a visible way to distinguish between 1 gift and 2 gift items, I won't be able to make the code work. Is there any way that you could add the number 1 at the end of each name of Products 1 to 10 and add the number 2 at the end of each name of Products 11 to 15? Then I could use the numbers to distinguish between the two different groups of products.

 

[DKTRL]

I don't think that this will work. Unless there is a visible way to distinguish between 1 gift and 2 gift items, I won't be able to make the code work. Is there any way that you could add the number 1 at the end of each name of Products 1 to 10 and add the number 2 at the end of each name of Products 11 to 15? Then I could use the numbers to distinguish between the two different groups of products.

That shouldn't a problem. please based on what you have suggest, and i will do my products code adjust at my side.


Thanks

 

[mumps]

OK. I will give it a try but please be patient. I'm not sure how long it will take and I may have some more questions.

 

[DKTRL]

OK. I will give it a try but please be patient. I'm not sure how long it will take and I may have some more questions.

Thanks !