0

I'm trying to develop an internal use form where individuals can make a selection from a drop down list that will filter another drop down list based on their choice. I have reviewed the options for indirect, dependent dynamic lists, but this does not seem feasible for a list that will potentially contain hundreds of different values with thousands (or more) permutations. The group before me created a bunch of macros to solve this issue, but I have to believe there is a simpler way to manage the information.

Is there a way that you can develop a drop down list from within a single cell? Any insight would be greatly appreciated.

UPDATE: Using part of Gary's code, I was able to solve part of my issue:

Sub Marco4()
    r = Range("H24").Value
    s = Application.VLookup(r, Worksheets("Data").Range("R2:T4"), 3, False)
    Range("B25").Select
    With ActiveCell.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=s
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

I'd like to use an existing drop down item instead of having the cell turn into a drop down itself, if possible.

Lee Carver
  • 35
  • 1
  • 7

1 Answers1

0

Say cell C2 contains a comma separated string. This simple macro will set the Data Validation of the activecell to that string:

Sub LeeCarver()
    Dim s As String
    s = Range("C2").Value
    With ActiveCell.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=s
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

For example:

enter image description here

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Ok. Sorry I don't have the ability to post pictures (yet), or I'd diagram this out. But let's say I had Column B with Rows 1, 2, and 3 (values: ABC | DEF | GHI) and Column C with Rows 1, 2, and 3 (values: 123,456 | 456,789 | 123,789). If Drop Down 1 selects ABC, would I just create a new value in that subroutine to vlookup the value of Drop Down 1 and then paste that in for the range of Drop Down 2? – Lee Carver Feb 04 '15 at 15:48
  • I updated the code in my original post of what I'm trying to put in. Here is a link to dummy mock up of what I'm trying to achieve: http://s27.postimg.org/hl5mbx3hv/excel.png – Lee Carver Feb 04 '15 at 16:16
  • @LeeCarver have you found the solution for this? – BBacon Jul 15 '17 at 21:06