On Monday, AlexJ showed us how to create a short or long drop down list in Excel. With his technique, users can see just the top customers, or all customers. That technique didn’t require macros — it was driven by a formula in the data validation source.
Today, Alex shares an automated version of the short or long data validation list technique. Here’s his description of how it works.
You can download the zipped Dynamic Data Validation Sample File from the Contextures website. The file contains macros, so enable them to use the dynamic drop down list.
Dynamic Data Validation Lists
For an Excel utility running at our office, users are required to enter a project number using a drop down list. There are thousands of these records in the data set, selecting from hundreds of project numbers. This means that the drop-down list is long, and therefore not very useful.
To address this, we determined that the user would usually select from a short list of active projects, but would also need to select from a long list of all projects or old projects.
There are a number of techniques using dependent data validation in Excel, but these usually require two selection boxes, we wanted to do this with only a single drop down selection. The technique presented allows the user to select from a default list of entries, or select a different list.
How It Works
The two lists are named — rng.DD1 for the new projects, and rng.DD2 for the full project list. The first cell in each list is a formula, that refers to the other list.
=”>> GOTO ” & $J$3
The cell with the drop down list is named rng.DD_Select.
The result cell, $E$5, calculates which list has been selected:
If the selected item matches the heading in cell J3, the result is rng.DD2, otherwise, the result is rng.DD1.
The Data Validation
The data entry cell has data validation configured for a list, and the following formula that refers to the result cell:
If the result in cell $E$5 is rng.DD1, the new project list is shown.
The data validation doesn’t require programming, but there is a small VBA routine triggered by the Change Event in cell B5. It tidies up the data entry cell, after a selection is made.
This routine will:
- Clear any entries from the list where the user has selected “——–”, or a list header like “—– xxxx ——–”
- Convert a selection like “>>>> GOTO NEW PROJECT LIST” to “NEW PROJECT LIST”
Here is the event code from the data entry sheet.
Private Sub Worksheet_Change(ByVal Target As Range) Dim str As String Dim strNew As String Const strMatch As String = ">> GOTO " If Target.Address = Me.Range("rng.DD_Select").Address Then str = Target.Value If str Like "-*" Then Target.ClearContents Else If str Like strMatch & "*" Then strNew = Right(str, Len(str) - Len(strMatch)) Target.Value = strNew End If End If End If End Sub