Dependent Drop Down List From a Row

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.

datavaldependsort02b

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.

dependentdropdownrow02

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.

dependentdropdownrow01

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.

dependentdropdownrow04

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.

dependentdropdownrow02

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.

dependentdropdownrow07

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.

=OFFSET(EmpLookup,MATCH(B2,EmpList,0)-1,1,1,
  COUNTA(OFFSET(EmpLookup,MATCH(B2,EmpList,0)-1,1,1,5)))

dependentdropdownrow05

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.

dependentdropdownrow01

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.

________________

2 comments to Dependent Drop Down List From a Row

  • Dave

    Wow! That's really neat! Thanks Debra!

  • Gerrit Snyman

    Hi people

    This was very helpful, thanks a lot.

    what I cannot find is a formula to jump to a empty cell after choosing something from a drop down menu. I have for instance an Expense drop down list. if someone choose FUEL from it, it must jump to an empty cell for FUEL where they can input the fuel amount. This way I can make sure that the expenses are allocated correctly.

    Regards and keep up the good work.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>