Dynamic Excel Drop Down List

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.

Long or Short List Macro

Today, Alex shares an automated version of the short or long data validation list technique. Starting in the next section, you can read his description of how this version 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.

Use a Single Drop Down

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.

Calculate Which List Selected

The result cell, $E$5, calculates which list has been selected:

=”rng.DD”&IF(rng.DD_Select=$J$3,2,1)

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:

=INDIRECT($E$5)

If the result in cell $E$5 is rng.DD1, the new project list is shown.

The Programming

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:

1. Clear any entries from the list where the user has selected “——–”, or a list header like “—– xxxx ——–”
2. Convert a selection like “>>>> GOTO NEW PROJECT LIST” to “NEW PROJECT LIST”

Excel Event Code

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

________________