Excel formula that will let me show a data range in a cell based on the value in another cell

0
votes

I am trying to create a form where someone can choose a value from a drop down list and it will create another dropdown list in the next cell based on the first cells value. The next cells dropdown list would need to reference a cell range on a separate worksheet and only show row 4-30 as a dropdown.

Is this possible? I tried using data validation with an indirect formula but I cannot define the cell ranges as the codes start with numbers.

Any suggestions would be helpful?

2
vbaexcelexcel-formula
Can you post something about how you tried INDIRECT? This is one of the more common use cases for INDIRECT, and it usually works well if you name the ranges correctly. – Byron Wall
Thank you for your responses. The Choose/Match function works well except that I have 427 columns that need to be matched which seems to be too much for data validation. – FarahS
For Indirect you need to name your ranges however apparently you cannot define names if they start with a number. All my lists are codes that start with numbers. – FarahS

2 Answers

1
votes

Here is a very simple example using VBA

Place this event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A1 As Range, B1 As Range
    Set A1 = Range("A1")
    Set B1 = Range("B1")
    If Intersect(A1, Target) Is Nothing Then Exit Sub
    Select Case A1.Value
        Case "animals"
            Call SetupDV(B1, "dog,cat,bird")
        Case "days"
            Call SetupDV(B1, "monday,tuesday,wednesday")
        Case "months"
            Call SetupDV(B1, "january,february,march")
    End Select
End Sub

and place this macro in a standard module:

Sub MAIN()
    Cells.Validation.Delete
    Call SetupDV(Range("A1"), "animals,days,months")
End Sub

Sub SetupDV(MyCells As Range, st As String)
    With MyCells.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:=st
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
    End With
End Sub

Then run MAIN

Whenever you make a selection from the pull-down in cell A1, the pull-down in cell B1 will adjust accordingly:

enter image description here

1
votes

There's a solution for this using formulas alone, without VBA code.

Assuming your data looks like this:

Dynamic validation list

  • First column selection list is Catgories, in A2:A4.
  • Second column selection list will be chosen as one of C2:C6 | D2:D6 | E2:E6, by the selection of a category.
  • In G2:G3 you can select a category.
  • H2:H3 will show the list according to the category selected in G respectively.

The validation list source for H2:H3 will be defined with this formula:

=CHOOSE(MATCH(G2,$C$1:$E$1,0),$C$2:$C$6,$D$2:$D$6,$E$2:$E$6)

Select H2:H3, go to Data Validation, choose List and put this formula in the Source. (The range reference will be updated for H3 automatically).

The Formula:

The MATCH part returns 1,2 or 3 for the category names in C1:E1. CHOOSE returns the range C, D or E respectively, according to the order they are supplied in the function.

Remarks:

The ranges can reference cells in another sheet by simply stating the sheet name Sheet2!C2:C6 in the validation list formula. In earlier versions of Excel you need to name these external ranges for it to work in a validation list.