Dynamic Excel Drop Down List

iconlistlong2 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.

DataValDynamic01b

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

DataValDynamic02

The cell with the drop down list is named rng.DD_Select.

DataValDynamic03

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.

DataValDynamic04

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)

DataValDynamic03b

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

DataValDynamic06

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”

DataValDynamic07

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

________________

You may also like...

6 Responses

  1. Leonid Koyfman says:

    Aex,
    Do you have a sample workbook to download?

    LK

  2. AlexJ says:

    Debra,
    Are you posting the wb somewhere?
    Aex

  3. Leonid, I’ve added a link in the first section of the blog post, so you can download Alex’s sample file.

    It’s also available on the Excel Sample Files page on the Contextures website:

    http://www.contextures.com/excelfiles.html#DV0056

  4. Teri says:

    Does this work with Excel 2010 using two different spreadsheets, one active (open) and one not?

  5. AlexJ says:

    Teri,
    Getting a list from a closed workbook is a whole other problem, with a couple of solutions, but not necessarily simple. Maybe have a linked range to the closed workbook would work best for you.

  6. Ralf says:

    I was wondering how i would be able to choose which drop down list is displayed going by a table.

    H1 H2 H3 H4 H5
    H1a H2a H3a H4a H5a
    H1b H2b H3b H4b H5b
    H1c H3c H4b

    Have the first drop down box show options (H1, H2, H3, H4 and H5)- This i can accomplish easy enough.
    then depending on which value you choose, show the options in the second dropdown box.
    So let’s say you pick H1 in the first dropdown box, then the second dropdown box should only show the list of option for H1 (H1a, H2a, H3a)

    If someone could help me on this one i would appreciate it very much.

Leave a Reply to Ralf Cancel reply

Your email address will not be published. Required fields are marked *