Thread: Refine a list of data based upon answers to dropdown questions in excel

I want to create an excel where I can have a bunch of yes/no questions in the form of dropdowns, and depending upon the combination of yes's and no's chosen, a giant list of data is refined to show only the data that applies to the exact combination of yes's and no's selected.

Here is how I am attempting to accomplish this thus far:

The first question is "Is the acquisition above the Simplified Acquisition threshold?" If "yes" is chosen from a dropdown, I have a cell to the right of the column displaying a predefined term using an IF function (i.e. =IF(B2="yes","OSAT",IF(B2="no","USAT",)). Therefore, if "yes" is chosen, the term OSAT is displayed in an adjacent column cell. If No, is selected, USAT is displayed instead. I intend to have numerous questions similar to this, each with answers that will display a different term depending on the dropdown answer selected. I will end up with a column filled with cells that have random terms in them.

I also have a list of 400 contract clauses in descending rows, in column A for example. For each clause in column A, I want to type all of the terms associated with its use in Column B, separated by commas or some other form of separation.

The overall goal is that if the user chooses "yes" in Question #1, the list of 400 clauses is refined so that only those clauses in column A with OSAT next to them will appear. This can either be a refinement of the actual 400 clause list, or a new list of all of the clauses that have OSAT associated with it in Column B. If I had a second question answered, the list would then further refine to only show the clauses that had both the term from question 1 AND question 2 located next to the clause in column B.

I do not know what excel function would be appropriate to have this functionality. I can get the question dropdowns working, and can get my predetermined terms to appear in an empty cell once the question is answered, but I cannot get a list to refine based upon matches to the list of terms that appear from answering questions.

Any help is appreciated. I have attached an excel sample titled "Testing". The yellow boxes are the dropdowns. In the example, when the answer is "yes" for both questions, I want a list of clauses that this answer combo would apply to displayed somewhere in the excel. In this particular case, a yes answer for both question should lead to the display of a list consisting of only the first clause (52.212-4) since this is the only clause with both OSAT and YDATA associated with it.

Testing.xls

This can be worked out in 2 ways -
1. Select the values for all the dropdowns. The corresponding text(s) will appear. Then click on a button and the corresponding clause list will be displayed somewhere on the sheet or on a different sheet.
2. As each value is being selected in the dropdown, the corresponding clause list will be displayed and depending on the changes to the dropdown, the list will get changed.

Here's a good place to start

Excel Data Validation
Create Dependent Lists
Create Dependent Lists
Create Dependent Lists With INDEX