Data Validation‎ > ‎

Sub-DV Lists

    
            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:

                                  LISTS
 ABC
1MGREmployeeManagers
2MGR1Emp1MGR1
3MGR1Emp2MGR2
4MGR1Emp3MGR3
5MGR1Emp4MGR4
6MGR2Emp5MGR5
7MGR2Emp6MGR6
8MGR3Emp7 
9MGR3Emp8 
10MGR3Emp9 
11MGR3Emp10 
12MGR4Emp11 
13MGR4Emp12 
14MGR4Emp13 
15MGR4Emp14 
16MGR4Emp15 

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:




SPECIFICATIONS:
  1. Named ranges are used for the Manager dropdown and the full Employee listing.
  2. The drop down for Employees lists only employees for the manager chosen
  3. The MGR column of the main table must be sorted ascending any time it is edited (Critical)
  4. When nothing has been chosen in Manager drop down, some sort of default message should appear in the Employee drop down
  5. Mismatches caused by changing the Manager selected after an employee is chosen should be dealt with without VBA.
SETUP:
  1. 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:

    =LISTS!$C$2:INDEX(LISTS!$C:$C,MATCH("zzz",LISTS!$C:$C))

                           

  2. Repeat that same formula syntax to create dynamic name ranges for the MGR (column A) and the EMPL (column B) ranges, too.                      

  3. To give ourselves a "default" message, put a message into an empty cell and name it Message:

  4. 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)))


    AVOIDING MISMATCHES
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.
  1. We create an ErrorCheck named formula:

    =Sheet1!$A2=INDEX(MGR, MATCH(Sheet1!$B2, EMPL, 0))


  2. Then use conditional formatting to change the text color if the employee and manager no longer match:

                      =NOT(ErrorCheck)




Nothing says "thanks" like a steak dinner!

PayPal - The safer, easier way to pay online!



Comments