We begin with a table of Divisions where each Division contains a list of Apps.
The goal is to have the user select from the first drop down list a Division name. Next, the user will select an App from the second drop down list. The key is to only display the apps associated with the selected Division from the first drop down list.
NOTE: In the real world, the list of Divisions and Apps would likely be placed on a separate (possible hidden) sheet, or the list would be placed somewhere far away from the data entry portion of the sheet where the user is not likely to ever find. For this example, we are placing the list close to the user-area to make the demonstration easier to follow.
Creating the First Drop Down List (Division)
The first drop down list will allow the user to select a Division. This one is simple.
We begin by creating a Data Validation drop down list for cell A5 that points to the Divisions listed in cells F4 through H4.
- Select cell A5
- Select Data (tab) -> Data Tools (group) -> Data Validation
- In the Data Validation dialog box, set Allow to “List” and Source to “=$F$4:$H$4”
- Click OK
To get the drop down list to appear for multiple rows, select A5 and copy/paste the contents to adjacent cells (ex: A6:A20).
Creating the Second Drop Down List (App)
As stated earlier, we want the second list of Apps to only show items based on the selected Division from the first drop down list.
Using the OFFSET function
We will use the OFFSET function to select Apps from one of the three listed Divisions.
If you’re not familiar with the OFFSET function, it allows us to start from a defined point then moves away from that point by a set number of rows and/or columns. This establishes a new start point. From here, we can select a set number of rows and/or columns.
The OFFSET function has the following syntax:
OFFSET(reference, rows, cols, [height], [width])
- reference – the cell or range of cells you want to base the offset (e. the starting point).
- rows – the number of rows down or up you want to move. A positive number moves down; a negative number moves up.
- cols – the number of columns right or left you want to move. A positive number moves right; a negative number moves left.
- [height] – the number of rows you want returned. This must be a positive number.
- [width] – the number of columns you want returned. This must be a positive number.
NOTE: For an explanation of the OFFSET function, see this post/video.
Ultimately, we need to create this formula in the Data Validation dialog box.
Since the Data Validation dialog box does not possess the IntelliSense feature that is so helpful when writing formulas, we will write and test our formula in a standard Excel cell.
Once we have the formula working, we will transfer it to the Data Validation tool for its final test.
Establishing the Starting Point
We will always begin our search for Apps from the upper-left corner of our table (cell F4).
Start the OFFSET function as follows.
=OFFSET($F$4We need to make this reference to cell A4 as an absolute reference so each copied/pasted version begins from the same point.
Moving Down or Up
Next, we tell OFFSET how many rows to move down or up. The Apps list begins 1 row below our start point, so we assign a value of 1 to the rows argument.
=OFFSET($F$4, 1Moving Right or Left
Now we tell OFFSET how many columns to move right or left.
This is determined by the user’s Division selection from the first drop down list.
For this, we can use the MATCH function to locate the selected Division in cell A5 from the list of Division in cells F4 through H4. We also need to match exactly (0 in the last argument).
=OFFSET($F$4, 1, MATCH($A5, $F$4:$H$4, 0)NOTE: We only “locked” the “A” part of the “A5” reference because we don’t want to point to other columns when we replicate this to adjacent cells, but we do want to point to other rows when creating additional drop downs. If that’s a bit unclear, stick with us because it will become clearer very soon.
If we highlight the MATCH portion of the formula and press F9, we can see MATCH returns a number indicating the discovered position in the search range.
NOTE: Remember to press CTRL-Z to undo your formula back to its original structure.
The Problem with MATCH
A minor issue we have with MATCH is that it counts cells beginning with the value 1.
This means that “Productivity” is in the first column of the data, “Game” is in the second column, and “Utility” is in the third column.
Because we’re trying to inform OFFSET how many columns to move to the right, these values are incorrect.
If we want “Productivity”, we don’t want to move left or right. If we want “Game”, we want to move 1 column to the right, and if we want “Utility”, we want to move 2 columns to the right.
We need to reduce the returned value of MATCH by 1 to adjust for this discrepancy.
=OFFSET($F$4, 1, MATCH($A5, $F$4:$H$4, 0) – 1Selecting the Height to Return
We dropped 1 row from the start point to land on the first App. We also moved right to the proper Division column.
The tricky part is to determine the number of rows to select. This will be the list of Apps that will drives the second drop down list.
Notice that our three lists do not contain the same number of items.
Temporarily, we will treat each list as if they contained 15 items. This will allow us to see if the [height] argument is working. Later, we’ll make the height selection dynamic so it will return the perfect number of Apps for our second drop down list.
=OFFSET($F$4, 1, MATCH($A5, $F$4:$H$4, 0) – 1, 15For any columns that contain less than 15 Apps, this will produce empty slots in the Data Validation drop down list. If you’re okay with that, you can leave the [height] argument set to the largest expected list length. This could be a problem if one list has hundreds of items while another list has only dozens.
Like I said a moment ago, we’ll start statically then make it dynamic later, so we eliminate the empty slots.
Selecting the Width to Return
We only ever want a single column of Apps to be returned by the OFFSET function, so we will set the [width] argument to 1.
=OFFSET($F$4, 1, MATCH($A5, $F$4:$H$4, 0) – 1, 15, 1)Setting Up Data Validation
Now that we have completed our test formula, it’s time to use the logic in a Data Validation drop down list.
- Select cell B5
- Highlight the OFFSET/MATCH formula displayed in the Formula Bar
- Copy the formula to memory
- Press ESC to get out of Edit Mode
- Select Data (tab) -> Data Tools (group) -> Data Validation
- In the Data Validation dialog box, set Allow to “List”
- In the Source field, paste the recently copied OFFSET/MATCH formula
- Click OK
Testing the OFFSET/MATCH Formula
If we select a Division from the first drop down list (A5), we then see the list of related Apps in the second drop down list (B5).
Because we are using a fixed [height] argument, we see blank slots for any Division where the Apps list is less than 15.
Getting the Perfect Height
To get a list of Apps that do not display blank slots at the bottom, we need to calculate the height of the selected Division’s Apps.
We need to take the previously hard-coded “15” for the [height] argument and make it dynamic.
Enter the COUNTA Function
A perfect way to determine the number of Apps in a give Division is to use the COUNTA function.
As a test, we select an unused cell in our sheet and write the following formula:
=COUNTA(F5:F19)This yields a result of 15.
If we point the COUNTA function to G5:G19, we get 12, and if we point the COUNTA function to H5:H19, we get 13.
We don’t want to hard-code the count range, we want the range to be based on the selected Division.
We can replace the hard-coded range with the same OFFSET formula used earlier.
=COUNTA(OFFSET($F$4, 1, MATCH($A5, $F$4:$H$4, 0) – 1, 15, 1)NOTE: the “15” defined in the [height] argument represents the largest number of Apps we would expect to see in any given Division. You should set this value to whatever number you believe you would never reach, such as =COUNTA(OFFSET($F$4, 1, MATCH($A5, $F$4:$H$4, 0) – 1, 500, 1).
Testing the COUNTA Formula
In cell A5, if we select “Productivity” we are returned a value of 15. If we select “Game” we are returned a value of 12, and if we select “Utility” we are returned a value of 13.
Updating the Data Validation Formula
Our final step is to update the formula previously placed in the Data Validation tool for cell B5 to include this dynamic logic for [height] calculations.
- Select the cell that contains the temporary COUNTA formula
- Highlight the COUNTA/OFFSET formula displayed in the Formula Bar
- Copy the formula to memory
- Press ESC to get out of Edit Mode
- Select cell B5
- Select Data (tab) -> Data Tools (group) -> Data Validation
- In the Source field, remove the “15” from the original formula and paste the recently copied COUNTA/OFFSET formula
- Click OK
=OFFSET($F$4, 1, MATCH($A5, $F$4:$H$4, 0) – 1, COUNTA(OFFSET($F$4, 1, MATCH($A5, $F$4:$H$4, 0) – 1, 15, 1)), 1)Testing the Updated Formula
If we select a Division form cell A5 that has less than 15 Apps, we are no longer presented with empty slots at the bottom of the Apps drop down list (B5).
Copying the Data Validation to Adjacent Cells
To replicate the second Data Validation drop down list to the row cells below cell B5:
- Select cell B5
- Press Copy
- Select your destination cells (ex: B6:B15)
- Right-click the highlighted range and select “Paste Special…”
- In the Paste Special dialog box, select Validation and click OK
Final Test
You are now able to select a cell from the “Select Division” column, select a Division, then click the corresponding “Select App” cell to be presented with the Apps for the selected Division.
Closing Comment
Granted, I did say that we would use a single formula. I never said it would be short and simple.
The nice thing is that with a small bit of practice, this formula is fairly easy to write. You could even copy/paste it form project to project, reusing it with just minor modifications.
I think it’s easier to stick to a single strategy for building these dependent drop down lists and get good at it, versus learning dozens of other techniques.
Yes, some of those other techniques may be more efficient or have more features, but unless you need fancy and exotic, this one will do just fine.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
