There is a sample file on my Contextures site, which creates dependent drop down lists from items that are sorted in columns. You can see the lists in the screen shot below. On the data entry sheet, you select a region from the first drop down, and only the customers in that region appear in the second drop down.
Data in Rows
Sometimes life isn’t perfect though, and you might not be able to get your data in nicely sorted columns. In my new sample file, the dependent lists get their items from a row.
In the screen shot below, there is employee information in a table, and each person’s row can have up to 5 skills listed. This is a formatted table, named tblEmpLU.
In the data entry sheet, there are drop down lists in columns B and C. First, select an employee name in column B, then see a list of their skills in column C. This is another formatted table, named tblAssign.
Name the Lists
To create a drop down list of employees, I named cells B2:B5 in the employee lookup table, as EmpList. Then, I used that list as the source for a data validation drop down list.
To create a lookup range, I selected all the data cells in the tblEmpLU table (B2:G5), and named that range EmpLookup. Because the range is based on a formatted table, it will automatically expand, if more rows are added to the table.
Create the Dependent Drop Down List
The second data validation list has an OFFSET formula that finds all the skills for the selected employee. You can see its arguments in the screen shot below.
Here’s how we’ll use those arguments:
- Reference: This is the starting point for finding the selected employee’s skills list. We’ll use the EmpLookup range as the reference
- Rows: Number of rows down from row 1 in the Reference, to get the employee’s data. We’ll use MATCH to get the row, and subtract 1. For example, if Lou’s info is in row 4 of the range, we need to go down 3 rows from row 1.
- Cols: 1 – Number of columns to go across, to find the starting point for the skills data
- Height: 1 – we want 1 row of data in the result
- Width: Number of skills entered for the selected employee (we’ll use COUNTA to get this number)
Here is the formula in the dependent data validation drop down, entered in cell C2. It’s colour coded to show how each of the arguments is calculated.
Test the Dependent Drop Downs
After you select a name from the first drop down, click the drop down arrow in the second drop down cell. The list shows only the skills for the employee that you selected.
Download the Sample File
To see the formulas, you can download the sample file from my Contextures website. Go to the Sample Files page, and in the Data Validation section, look for DV0060 – Dependent List From Row Items. The zipped file is in xlsx format, and there are no macros.