Same Data (date) Validation List for 2 cells; 1 cells to be greater than other cell

[JackDanIce]

Hi,

I have a named range [Range_Date] filled with dates Jan 2019 - Dec 2020, incrementing monthly.

On another sheet I have two cells with data validation for list with values [Range_Date], where one is the start date and other end date.

Is there a way to set data validation to ensure end date greater than start date as well as include drop down list?

TIA,
Jack

 

[AlphaFrog]

In this scenario...
Range_Date is on Sheet1 and is the Data Validation list for Sheet2 A1
Range_Date2 is on Sheet1 and is the Data Validation list for Sheet2 A2

To start, Range_Date and Range_Date2 can be the same list. The code will change Range_Date2.

To install the code below:
- Right-click on the sheet tab that has the DropDown lists (Sheet2)
- Select View Code from the pop-up context menu
- Paste the code from below in the Worksheet's code module
- Change the Sheet names and Data Validation cell addresses to suit

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    [color=darkblue]Dim[/color] x [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]If[/color] Target.Address(0, 0) = [B]"A1"[/B] [color=darkblue]Then[/color]
        Range("A2").Value = ""
        [color=darkblue]If[/color] Target <> [color=darkblue]Empty[/color] [color=darkblue]Then[/color]
            [color=darkblue]With[/color] [B]Sheets("Sheet1")[/B].Range("Range_Date")
                x = Application.Match(Target, .Cells, 0)
                [color=darkblue]If[/color] x < .Count [color=darkblue]Then[/color]
                    ThisWorkbook.Names.Add Name:="Range_Date2", RefersToR1C1:=.Cells(x + 1).Resize(.Count - x)
                [color=darkblue]Else[/color]
                    ThisWorkbook.Names.Add Name:="Range_Date2", RefersToR1C1:=""
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

 

[JackDanIce]

Thank you @AlphaFrog that's great, however, is there a non-VBA solution?

 

[Peter_SSs]

Would this be any use?
I have my Start Date Validation cell as A1 on Sheet2 and End Date DV cell as A2 on Sheet2.

On Sheet 1 I have the list of dates in A1:A24 & formulas as shown, copied down.

Excel Workbook
	A	B	C
1	Jan-19	Jan-19
2	Feb-19	Feb-19
3	Mar-19	Mar-19
4	Apr-19	Apr-19
5	May-19	May-19
6	Jun-19	Jun-19
7	Jul-19	Jul-19
8	Aug-19	Aug-19
9	Sep-19	Sep-19
10	Oct-19	Oct-19
11	Nov-19	Nov-19
12	Dec-19	Dec-19
13	Jan-20	Jan-20	Jan-20
14	Feb-20	Feb-20	Feb-20
15	Mar-20	Mar-20	Mar-20
16	Apr-20	Apr-20	Apr-20
17	May-20		May-20
18	Jun-20		Jun-20
19	Jul-20		Jul-20
20	Aug-20		Aug-20
21	Sep-20		Sep-20
22	Oct-20		Oct-20
23	Nov-20		Nov-20
24	Dec-20		Dec-20
Sheet1

I have then created 2 new named ranges (via the name manager on the Formulas ribbon tab) & used those in my data validation as shown.

Excel Workbook
	A	B
1	Dec-19	Start
2	May-20	End
Sheet2

#VALUE!

</td></tr></table></td></tr></table>

 

[JackDanIce]

Helper column d'oh (forehead palm!) and have absolutely no reason not to have a second date range!
@thanks Peter_Ss