+ Reply to Thread
Results 1 to 16 of 16

Data validation - drop down list - latest date

  1. #1
    Registered User
    Join Date
    07-04-2017
    Location
    Belgium
    MS-Off Ver
    365 ProPlus
    Posts
    81

    Data validation - drop down list - latest date

    Hey all,

    I created a range "dates" with an x-amount of rows.

    I would like to offer 2 drop down lists (via data validation):

    Start date
    =dates

    End date.
    The end date should be >= start date AND <= latest date in the range "dates".

    =AND(B5>=B4; B5<=MAX(dates))

    This formula works, but only when you select CUSTOM in the data validation.
    It doesn't work when you chose LIST.

    Can any1 help me?
    Attached Images Attached Images
    Last edited by dunnobe; 11-14-2017 at 06:27 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,561

    Re: Data validation - drop down list - latest date

    I'd use two named ranges, like this. DV/formulae in shaded cells. Happy to explain if this meets you needs.
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    07-04-2017
    Location
    Belgium
    MS-Off Ver
    365 ProPlus
    Posts
    81

    Re: Data validation - drop down list - latest date

    Hey Glenn,

    Thank you for your quickly reply.

    Isn't there a solution without having to make an extra column/range? I don't have much "room" for this in my current assignment.
    Can the IFERROR function be integrated in the list formula?

    Google doesn't help much although this should be a pretty common question.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: Data validation - drop down list - latest date

    Alternate method.

    Dynamic named range formula for end date.
    Assuming dates in Column A and start date in G2.
    =OFFSET(Sheet1!$A$2,MATCH(Sheet1!G2,dates,0),,ROWS(dates)-MATCH(Sheet1!G2,dates,0))
    Attached Files Attached Files
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,561

    Re: Data validation - drop down list - latest date

    Very neat CK76. One tweak. Use a dynamic formula for the start dates:

    =Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$1000,COUNTA(Sheet1!$A$2:$A$1000))

  6. #6
    Registered User
    Join Date
    07-04-2017
    Location
    Belgium
    MS-Off Ver
    365 ProPlus
    Posts
    81

    Re: Data validation - drop down list - latest date

    Hey CK76,

    Where exacly do you put the OFFSET formula. I can't find this in your sheet, nor the end_date range that you used in the data validation list.
    Did you only create this in the Name Manager?

    (I've never used OFFSET before).

    Thank you for your help,

    Bram

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: Data validation - drop down list - latest date

    Go to formula ribbon tool and find name manager. Named range is there. Formula is used in the named range.

    As Glenn suggested, use dynamic named range for "dates" as well.

    0.JPG

  8. #8
    Registered User
    Join Date
    07-04-2017
    Location
    Belgium
    MS-Off Ver
    365 ProPlus
    Posts
    81

    Re: Data validation - drop down list - latest date

    I guess that your solution is similar to this:

    https://www.excel-university.com/dat...ion-drop-down/

    I'll see if I can get it to work. Getting a headache now :p

    Big it up for both of you!

  9. #9
    Registered User
    Join Date
    07-04-2017
    Location
    Belgium
    MS-Off Ver
    365 ProPlus
    Posts
    81

    Re: Data validation - drop down list - latest date

    Hi guys,

    A small question for a beter understanding.
    How do I make sure that the end date can be the same as the start date?

    The end date is always atleast 1 day after the start date.
    Last edited by dunnobe; 11-13-2017 at 11:04 AM.

  10. #10
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: Data validation - drop down list - latest date

    Hmm? In your original post you had >= start date.

    So it should be >start date?

    In that case just add 1 to start date.

    I.E. MATCH(Sheet1!G2+1,dates,0)

  11. #11
    Registered User
    Join Date
    07-04-2017
    Location
    Belgium
    MS-Off Ver
    365 ProPlus
    Posts
    81

    Re: Data validation - drop down list - latest date

    Hey CK76,

    Mmm, I can't get this +1 thing to work.
    When I do +1, the end date is atleast +2 days ahead of the start date.
    When I do -1, the drop down list freezes.

    Could you or Glenn Kenney have a look?

    (I added test.xlsx to the attachments. The info is in the worksheet called 'Summary'.)
    (Sorry, still learning a lot.)

    Best regards,

    Bram
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,820

    Re: Data validation - drop down list - latest date

    Correction: I get freeze problem too!

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    24,820

    Re: Data validation - drop down list - latest date

    Try

    for "End_date"

    =OFFSET(Table1[Date lg.],MATCH(Summary!$B$4,dates,0)-1,,ROWS(dates)-MATCH(Summary!$B$4,dates,0)+1)

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,561

    Re: Data validation - drop down list - latest date

    Like this, maybe??

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,561

    Re: Data validation - drop down list - latest date

    I see JT has jumped in d got there while I was eating my breakfast!! Same solution.

  16. #16
    Registered User
    Join Date
    07-04-2017
    Location
    Belgium
    MS-Off Ver
    365 ProPlus
    Posts
    81

    Re: Data validation - drop down list - latest date

    Thanks guys!

    I knew I had to work with the 1 or +1 so I definitely learned something.

    +1 rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 05-20-2017, 10:29 AM
  2. Replies: 3
    Last Post: 07-05-2015, 12:45 AM
  3. Replies: 0
    Last Post: 04-24-2015, 02:27 PM
  4. Replies: 1
    Last Post: 09-05-2012, 11:39 AM
  5. [SOLVED] Macro to to select latest date in cell with validation list consisting of dates.
    By rocksan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-23-2012, 04:41 AM
  6. Replies: 4
    Last Post: 05-17-2012, 04:40 PM
  7. Autocomplete Date Validation List drop-down
    By adds007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-16-2011, 02:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1