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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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