Drop Down Box ... There must be a better way and need Direction

[thhynes]

I have a Dropdown box for "YEAR", currently, there are years 2020,2021,2022,2023
I have another drop-down box for LEVEL selection - Catastrophic, Bronze, Silver, Gold
Based on those two selections - There is a drop-down box that uses =INDIRECT($C$11) - this then displays all of the plans for the year selected and for the metal level

I would like to improve this to show ONLY those level plans for the year selected.
I have the plan names organized in a Table for instance
The reason being is that the list of selections is getting too long, some plans offered one year and not another, and instead of scrolling down the list in the drop down box to choose from
it would just display the plans for that year under the level.

Would redoing the tables such as

Bronze 2020
Bronze 2021
Bronze 2022
Bronze 2023 make more sense?

Bronze

Plan Year 2020

Bronze HMO Pathway X Enhanced Tiered

Bronze PPO Pathway X

Bronze PPO Standard Pathway X

Bronze PPO Standard pathway X for HSA

Plan Year 2021

Bronze HMO BlueCare Prime

Bronze HMO Pathway Enhanced Tiered

Bronze PPO Standard Pathway

Bronze PPO Standard Pathway for HSA

 

[Mortiray]

You could do it the way you described but then you'd need to set the data validation list manually for every different box. Let me do some tinkering and see if I can find you a solution

 

[Mortiray]

Alright. I've got a solution for you, but it's complex. Please let me know if any of my explanation is confusing. Any of the names and positions can be changed, but you have to make sure their references are changed everywhere else.

Step 1: Make a new excel sheet named PlanData, and in that sheet, make a table of all your plans (this may already be done, but the way it's formatted is important). For this, I'm going to name the table "PlanGrid". This table will have four columns as shown below - one titled Year, one titled Level, one titled Plan Description, and one titled YearLevel. For the columns Year, Level, and Plan Description, fill in all the plan info you have. The column titled YearLevel needs to be filled with the formula

=[@Year]&[@Level]

. This fourth column is very important. This table should be located at A1.

Step 2: Create three pivot tables (Insert -> PivotTable) in the new sheet based on the table you just created. The first one will be at F1, the second at G1, and the third at H1. On the F1 PivotTable, drag the Year field to Rows. On the G1 PivotTable, drag the Level field to Rows. On the H1 PivotTable, drag the YearLevel field to Rows, then drag the Description field to Rows below the YearLevel Field. With data filled in, your sheet should look something like this:

This is all the info you need to structure your dropdowns properly
Now, you want three dropdowns: One for the Year, One for the Level, and one for every plan matching that year and level (I'll call this one the Plan dropdown). Figure out where you want these to be located in your main sheet, and for each dropdown, select all the cells you want to include that dropdown. Then, go to Data -> Data Validation, select List from the Allow dropdown, and paste the following formulas into the Source field. These formulas are going to assume the dropdowns are in the A and B column.

For the Year dropdown:

=OFFSET(PlanData!F2, 0, 0, COUNTA(PlanData!$F:$F)-3, 1)

For the Level Dropdown:

=OFFSET(PlanData!G2, 0, 0, COUNTA(PlanData!$G:$G)-3, 1)

For the Plan Dropdown:

=IF(COUNTIF(PlanData!$D:$D, A1&B1) > 0, OFFSET(INDIRECT("PlanData!$H$" & MATCH(A1&B1, PlanData!$H:$H, 0)), 1, 0, COUNTIF(PlanData!$D:$D, A1&B1), 1), "No Plan Found")

Let me know if you have any questions regarding this process.

EDIT: An aside, these formulas should work no matter how many times you add different plans to the table. One thing to note is whenever you modify the plan table, you'll need to click on each of your pivot tables and go to PivotTable Analyze -> Refresh to make sure they stay updated.

 

[thhynes]

Thank you for this, much appreciated