Drop Down List Based On A Cell Value

[Jojothemonk3y]

Hi,

I'm looking for some help with drop down lists....or any other suggesstions are welcome!

So in a table, a cell will be populated with a number value and i need the input of another cell to be restricted based on that value. The restriction will be above or below 10,000. I was thinking that a dependent drop down list would be the best solution...but cant seem to get it to work. Can anyone help or offer any solutions? So to be clear:


ex1
B1 B2
900 A or B

ex2
B1 B2
11000 X or Y

Thanks!

 

[steve the fish]

How is the 900 or 11000 getting added to the cell B1? User entry? Formula? If user entry try this in the appropriate sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range, str As String

Set rng = Intersect(Range("B1"), Target)

If Not rng Is Nothing Then
    If rng.Value > 10000 Then
        str = "X,Y"
    Else
        str = "A,B"
    End If
    With rng.Offset(1, 0).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=str
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End If
    
End Sub

 

[Scott T]

In Data validation
pick list
Where I1:I2 is the list for over 10K and H1:H2 is the list for under.
You did not say what should happen if it is equal to 10K. So if needed change from > to >=.

in source

=IF(B1>10000,I1:I2,H1:H2)

 

[Jojothemonk3y]

Thanks both for the help...problem solved!