Sample File: DV-Mini Dropdowns from Large List2.xls
This is rapidly becoming one of my favorite techniques, a merging of the ideas found in Multiple Categories
and in Single Name List.
Basically, we want a DV list of "options" that go with chosen category, but we really
don't want to use multiple columns and lists and multiple named ranges. We just want to enter a category and an option...one big two-column table. For instance, the employees that report to different managers:
Then we'd like a Data Validation drop down of our managers and have the second drop down list of employees create itself
from the manager chosen:
- Named ranges are used for the Manager dropdown and the full Employee listing.
- The drop down for Employees lists only employees for the manager chosen
- The MGR column of the main table must be sorted ascending any time it is edited (Critical)
- When nothing has been chosen in Manager drop down, some sort of default message should appear in the Employee drop down
- Mismatches caused by changing the Manager selected after an employee is chosen should be dealt with without VBA.
- On the LISTS page, create the initial list of managers. I like to use dynamic name range formulas so as you add more managers to the column C listing, the named range expands itself. The formula I use most often is:
- Repeat that same formula syntax to create dynamic name ranges for the MGR (column A) and the EMPL (column B) ranges, too.
- To give ourselves a "default" message, put a message into an empty cell and name it Message:
- Finally, we create a named FORMULA to do the heavy lifting and create the mini-dv lists out of the EMPL list once a MGR is chosen.
It is critical that before you do this, you switch to and select the first cell that will be using this named formula. So on Sheet1, we select B2:
Then we create the MyEmployees named formula with this syntax:
=IF(TRIM(Sheet1!$A2)="", Message, INDEX(EMPL, MATCH(Sheet1!$A2, MGR, 0)) : INDEX(EMPL, MATCH(Sheet1!$A2 & "zzz", MGR)))
It is likely at some point that a mismatch will occur. This is caused by selecting a manager, then an employee, then changing the manager choice. Oops!
We will use conditional formatting to hide the text anytime a mismatch occurs.
- We create an ErrorCheck named formula:
=Sheet1!$A2=INDEX(MGR, MATCH(Sheet1!$B2, EMPL, 0))
- Then use conditional formatting to change the text color if the employee and manager no longer match:
Nothing says "thanks" like a steak dinner!