Drop Down List Based On A Cell Value

Jojothemonk3y

New Member
Joined
Feb 1, 2017
Messages
5
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!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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:

Code:
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
 
Last edited:
Upvote 0
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
Code:
=IF(B1>10000,I1:I2,H1:H2)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top