Narrow Range based on Selection in Another Range

[Guest]

I have two validation cells that refer to the same range.
However, when you select a number in the first cell, I want the second cell
to only show numbers equal or higher than the number selected in the first
cell.
Do I need two ranges? How can I create a range that equals or is higher than
the number selected in the first cell?

Example: I have a range named GOLD with a range of 30 - 70.
The users selects 40.
When the user selects the 2nd cell, I want the range to only show 40 - 70.

Any help would be appreciated. Thanks!

 

[Gord Dibben]

See Debra's site for dependent DV lists.

http://www.contextures.on.ca/xlDataVal02.html


Gord Dibben MS Excel MVP

 

[Guest]

I tried a worksheet selection_Change macro, Maybe this will help if Gords
suggestion is not suitable.

Sub worksheet_SelectionChange(ByVal target As Range)

Dim rng As Range, r As Long, nr As Long, SrtR As Long
Dim col As Integer, delRng As Range
Set xRng = Range("Gold")
Set rng = Range("Platinum")
col = 3: SrtR = 4
rng.Value = xRng.Value: nr = xRng.Rows.Count
For Each c In target
If Union(c, xRng).Address = xRng.Address Then
r = ActiveCell.Row
Set delRng = Range(Cells(SrtR, col), Cells(r, col))
delRng.ClearContents
End If
Next c
End Sub

Regards
Peter

 

[Guest]

Hi Gord,
I've gone through her entire site it seems.
What I'm looking to do is VARY the 2nd range, based on the entry of the
first range.
So if the first range has 20 - 80 available, and 40 is selected, I want the
2nd range to have on 40 - 80 available.
I tried this with a formula in a column to the right of the first range,
which only displays the values equal to or higher than the number selected
(=IF($F$4<=I1,I1,""). But when I go to the 2nd validation field, all the
blank spaces still show up, although Ignore Blanks is checked.
So, I thought I'd try another way and create a new range based on the
selection from the first range starting in row 1 so there would be no blank
spaces in the 2nd drop down.
Any suggestions?