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

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

  1. #1
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,277
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

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

    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


  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,050
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

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

    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:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim x As Variant
        If Target.Address(0, 0) = "A1" Then
            Range("A2").Value = ""
            If Target <> Empty Then
                With Sheets("Sheet1").Range("Range_Date")
                    x = Application.Match(Target, .Cells, 0)
                    If x < .Count Then
                        ThisWorkbook.Names.Add Name:="Range_Date2", RefersToR1C1:=.Cells(x + 1).Resize(.Count - x)
                    Else
                        ThisWorkbook.Names.Add Name:="Range_Date2", RefersToR1C1:=""
                    End If
                End With
            End If
        End If
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,277
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

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

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


  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,385
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

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

    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.

    Sheet1

    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-20 May-20
    18Jun-20 Jun-20
    19Jul-20 Jul-20
    20Aug-20 Aug-20
    21Sep-20 Sep-20
    22Oct-20 Oct-20
    23Nov-20 Nov-20
    24Dec-20 Dec-20

    Spreadsheet Formulas
    CellFormula
    B1=IF(OR(A1,A1,"")
    C1=IF(OR(Sheet2!A$1="",A1>Sheet2!A$1),A1,"")


    Excel tables to the web >> Excel Jeanie HTML 4


    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.


    Sheet2

    AB
    1Dec-19Start
    2May-20End

    Data Validation in Spreadsheet
    CellAllowDatasInput 1Input 2
    A1List =Start_Dates
    A2List =End_Dates
    Names in Formulas
    CellNameApplies to
    A1Start_Dates=Sheet1!$B$1:INDEX(Sheet1!$B$1:$B$24,COUNT(Sheet1!$B$1:$B$24))
    A2End_Dates=INDEX(Sheet1!$C$1:$C$24,COUNTBLANK(Sheet1!$C$1:$C$24)+1):Sheet1!$C$24


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,277
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

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

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


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •