Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Narrow Range based on Selection in Another Range
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Narrow Range based on Selection in Another Range
See Debra's site for dependent DV lists.
http://www.contextures.on.ca/xlDataVal02.html Gord Dibben MS Excel MVP On Sun, 1 Jul 2007 06:22:01 -0700, David wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Narrow Range based on Selection in Another Range
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 "David" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Narrow Range based on Selection in Another Range
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? "Gord Dibben" wrote: See Debra's site for dependent DV lists. http://www.contextures.on.ca/xlDataVal02.html Gord Dibben MS Excel MVP On Sun, 1 Jul 2007 06:22:01 -0700, David wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
Identifying a selection of a selection of a range | Excel Worksheet Functions | |||
Range Selection | Excel Discussion (Misc queries) | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
Define a range based on another named range | Excel Worksheet Functions |