Excel: Dropdown list dependant on other dropdown list

by

Update as promised:

When you’re using a List for validation, you have to input a range as shown below.

Excel2

The OFFSET function allows to to dynamically set a range based on its input criteria.

If you consider this:

=OFFSET(C1,0,0,1,1)
  • Argument 1 = Anchor cell
  • Argument 2 = Number of rows to move, you can use minus number here to move rows up and positive numbers to move down
  • Argument 3 = Number of columns to move. Negative is left, positive to the right.
  • Argument 4 = Height of the range (can’t be negative and is optional, default is 1)
  • Argument 5 = Width of the range (can’t be negative and is optional, default is 1)

In this instance, the range returned would be C1 as we have no row or column offset and height and width is set to 1

The MATCH function will return an index of where a value appears in a range of cells (range must be either 1 cell wide or 1 cell high)

Cell Screenprint

Based on the above screen print =MATCH("Group2",D1:F1,0) will return 2, as “Group2” appears in the second cell in the D1:F1 range. (“Group1” would return 1, “Group3” would return 3, and “Group4” would return #N/A as it doesn’t exist).

So based on that we can put the MATCH function in as our 2nd argument in the OFFSET function, and pick the column that matches the first argument in the MATCH function.

=OFFSET(C1,0,MATCH("Group2",D1:F1,0),1,1) will return back range E1 as we’ve shifted the columns by 2 from C1 because of the MATCH

=OFFSET(C1,1,MATCH("Group2",D1:F1,0),3,1) will now return back E2:E4 as we’ve increased the height of the range to 3 and the row offset to 1.

And finally we can change the “Group2” value in the MATCH function to a cell value that will mean the range will dynamically change.

Here I’ve used Cell A2 =OFFSET(C1,1,MATCH(A2,D1:F1,0),3,1) so whatever value is in cell A2 will be used to offset the range.

And the last thing to do is to put the dynamic range into the validation (I used B2)

Validation Example

This will dynamically set the validation range.

Validation Range

When I’m using OFFSET function with multiple arguments and I’m not sure that it’s returning back the right range, I wrote a small helper User Defined Function that I just put in a VBA module.

Public Function GetAddress(rng As Range) As String
GetAddress = rng.Address
End Function

This allows me to put the offset formula in and it will return back the range address. So I can make sure it’s right.

Get Address Example

There may be a built in function for this, but I’ve never found it.

More Related Contents:

  1. Excel VBA – How to add dynamic array formula
  2. TEXTJOIN for xl2010/xl2013 with criteria
  3. What is the difference between “Form Controls” and “ActiveX Control” in Excel 2010?
  4. Check if the file exists using VBA
  5. Difference between date and time w/out work week Excel
  6. How can I create a progress bar in Excel VBA?
  7. Combine 2 Excel tables into one appending the data?
  8. Compare double in VBA precision problem
  9. Row count on the Filtered data
  10. Excel VBA Macro: User Defined Type Not Defined
  11. Automate IE via Excel to fill in a dropdown and continue
  12. Run-Time error 1004 Excel 2013
  13. Pasting an Excel range into an email as a picture
  14. Excel VBA to Export Selected Sheets to PDF
  15. Controlling Internet Explorer local intranet using Excel VBA
  16. How to automatically insert a blank row after a group of data
  17. How to convert a Google Docs-File to an Excel-File (XLSX)
  18. Rounding in MS Access
  19. Count unique values in Excel
  20. Get File Path (ends with folder)
  21. Get length of array?
  22. Email a single attachment from folder of files each to a different person
  23. Bad data selection using VBScript , on date field values
  24. Sending object array to variant works before August 2019 Windows update, but fails after update
  25. Find last row in range
  26. How to get the path of current worksheet in VBA?
  27. How to open a workbook specifying its path
  28. Excel, save and close after run
  29. VBA: Modify chart data range
  30. Get parent folder path from file path using cell formula

Leave a Comment