Autofill Data Validation boxes.

[Ancient Wolf]

I haven't been able to find anything on this issue, and could really use the help.

I work in a call center, and created a spreadsheet that has a list of questions that we ask customers on the phone, and once the questions have been answered, will leed users to a specific product number which will in turn help users find the right product, or package, for the customer on the phone. I am now trying to add in the reverse of that. I need to be able to type in a product number into a specified cell, and then have the related questions already filled, so that the user can then skip those questions and fill in the blanks for what questions may not be answered by the input of the product number. The question answers are all Data Validation drop down lists. I know in one section I will want a table that basically answer which cells need to be answered and in which way. I also know how to use index - match and vlookup formulas to pull from the table. My problem is I don't know how to take that data and place it in the appropriate cells.

I considered using the formulas inside the DV boxes, but I know that that will, at best, only limit the selection to the one answer. Users would still have to click on the DV drop down list to select the appropriate answer which means users would ask the customer a question that we already had the answer to. I also considered making a button that will copy and paste the answers, but not sure how to make a macro or VBA code that can change which cells it would copy and paste into. Also, the questions are not exactly one right below the other. Many times, other questions can be between the ones that need to be filled.

So I guess in short, how do I make a formula or VBA code that will look at cell A2, see that it is product 200, find the answers to the questions, and then paste the answers where they need to go without having to do anything more than type in 200 into A2.

 

[GlennUK]

You can have formulas in cells before you apply Data Validation, and they can calculate answers the way you want. Does that help?

 

[Ancient Wolf]

Thank you for responding, GlennUK. I did not know that? I may be able to work with that then. Can you enter the formulas after the DV has been placed or does the formula need to be entered into the cell first?

Yes. I think I see now. Thank you very much.

 

[Ancient Wolf]

Okay, I just found a huge problem with entering formulas into the cells I want to control with the DV drop down lists.The moment I select anything from the drop down list, the formula is gone. I may also mention that I have a Clear button that clears out all the cells once, the user finishes with the sheet, so that when they get the next call they are starting fresh.

 

[Asator]

You could maintain dynamic lists that act as the source data for your validation drop downs, that are updated when any of the fields change.

i.e.

Set product ABC, field 1 now has options D E F, field 2 has options G H I. Selecting D from field one then further narrows down the options available in field 2.

Make sense?

 

[Ancient Wolf]

If you are talking like putting in if formulas into the Data Validation, such =if(b3=d,h), then I have though of that, but if I do it that way, people would still have to click on the cell to select from the limited answers. That defeats the purpose of the auto-fill. I also learned that different combinations of answers can bring up the same product number. Product 200 can have 3 or more combinations. So I can't see index - match, or vlookup formulas working. Could it be that because of how the sheet is designed now, that there may not be an answer other than having subsitute cells next to the DV cells that I can put the formulas in?