none
Selective Data Validation List RRS feed

  • Question

  • I have a lookup range on an Excel worksheet (MyLookups) with three columns (code, description and flag). On my data entry worksheet, I want to create data validation on one cell. I want the user to be presented with a list of all "descriptions" in MyLookups where the "flag" column is "Y".

    In other words I'm trying to create a user-configurable lookup range, which allows them to exclude certain items from appearing in the in-cell dropdown list, if they change the "flag" column to "N".

    Thanks!


    • Edited by Marak60 Monday, November 21, 2011 8:09 PM
    Monday, November 21, 2011 8:05 PM

Answers

  • GS,

    Thanks for that excellent solution, but in my case it won't work. The application user has to be able to just modify the lookup table (changing "Y" to "N", or vice versa, at will). They can also add new values to the lookup table, settting the flag either "Y" or "N" or delete items from the lookup table. I can impose range constraints on the entire table, but I can't count on them to sort the list by "Flag", and I don't want to use VBA to sort them on the fly (there are dozens of lookup tables that will have to use this mechanism).

    I think this is unsuited for a VBA solution and was hoping to get an expression or formula that I could plug in to the cell validation.

    I will keep your solution in mind, however, as I can see that it would be useful under other circumstances.

    Thanks!

    -Mark

     

    • Proposed as answer by danishaniModerator Saturday, January 28, 2012 4:06 AM
    • Marked as answer by Marak60 Saturday, January 28, 2012 5:03 AM
    Friday, November 25, 2011 5:13 PM

All replies

  • Monday, November 21, 2011 11:29 PM
    Moderator
  • GS,

    Thanks for your response. I have seen this page before, but I'm not sure how it solves my particular problem. That page deals with substituting entire lists for cell Y, based on the selection in a cell X.

    My lookup table (named range = "MyLookup") looks like this:

    code      description      flag
    ------      --------------      ----
    NJ          New Jersey      Y
    NY          New York         N
    OH         Ohio                 Y
    ID           Idaho              N

    I want to create a formula or expression that can be added to the Data Validation list that will only show the items on the list where flag = Y. In this case, the user would only see "New Jersey" and "Ohio" in the Data Validation in-cell drop down.

    If a technique for doing this is shown on that page, I'm just not seeing it, and would appreciate a bit more specificity.

    In any event, thanks for taking the time to answer.

    -Mark

    Tuesday, November 22, 2011 7:14 PM
  • In H1 thru H4 enter:

    NJ
    OH
    NY
    ID

    Note that the "Y" values precede the "N" values.  In the worksheet code area insert the following event macro:

     

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A2 As Range
    Set A2 = Range("A2")
    If Intersect(A2, Target) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If A2.Value = "Y" Then
        Call MacroY
    Else
        Call MacroN
    End If
    Application.EnableEvents = True
    End Sub

     

    and in a standard module insert the following macros:

     

    Sub MacroY()
    With Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$H$1:$H$2"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    End Sub

     

    Sub MacroN()
    With Range("A1").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$H$3:$H$4"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    End Sub

     

    The user will enter either "Y" or "N" in cell A2.  The event macro detects the entry and establishes the DV list for A1 accordingly.


    gsnu201111
    Tuesday, November 22, 2011 10:38 PM
    Moderator
  • GS,

    Thanks for that excellent solution, but in my case it won't work. The application user has to be able to just modify the lookup table (changing "Y" to "N", or vice versa, at will). They can also add new values to the lookup table, settting the flag either "Y" or "N" or delete items from the lookup table. I can impose range constraints on the entire table, but I can't count on them to sort the list by "Flag", and I don't want to use VBA to sort them on the fly (there are dozens of lookup tables that will have to use this mechanism).

    I think this is unsuited for a VBA solution and was hoping to get an expression or formula that I could plug in to the cell validation.

    I will keep your solution in mind, however, as I can see that it would be useful under other circumstances.

    Thanks!

    -Mark

     

    • Proposed as answer by danishaniModerator Saturday, January 28, 2012 4:06 AM
    • Marked as answer by Marak60 Saturday, January 28, 2012 5:03 AM
    Friday, November 25, 2011 5:13 PM