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

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,674
Office Version
365
Platform
Windows
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,267
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

Code:
[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]
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,170
Office Version
365
Platform
Windows
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
ABC
1Jan-19Jan-19 
2Feb-19Feb-19
3Mar-19Mar-19
4Apr-19Apr-19
5May-19May-19
6Jun-19Jun-19
7Jul-19Jul-19
8Aug-19Aug-19
9Sep-19Sep-19
10Oct-19Oct-19
11Nov-19Nov-19
12Dec-19Dec-19
13Jan-20Jan-20Jan-20
14Feb-20Feb-20Feb-20
15Mar-20Mar-20Mar-20
16Apr-20Apr-20Apr-20
17May-20May-20
18Jun-20Jun-20
19Jul-20Jul-20
20Aug-20Aug-20
21Sep-20Sep-20
22Oct-20Oct-20
23Nov-20Nov-20
24Dec-20Dec-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
AB
1Dec-19Start
2May-20End
Sheet2
#VALUE!
</td></tr></table></td></tr></table>
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,674
Office Version
365
Platform
Windows
Helper column d'oh (forehead palm!) and have absolutely no reason not to have a second date range!
@thanks Peter_Ss
 

Watch MrExcel Video

Forum statistics

Threads
1,102,202
Messages
5,485,326
Members
407,496
Latest member
PttrsnMrgn

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top