+ Reply to Thread
Results 1 to 5 of 5

Macro to to select latest date in cell with validation list consisting of dates.

  1. #1
    Registered User
    Join Date
    04-05-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    88

    Macro to to select latest date in cell with validation list consisting of dates.

    Hi guys, need help in a what seems to be simple problem,

    In a worksheet I have 6 cells B7, E7, F7 etc. which are data validation cells. They are in a list form consisting of date values. I run a macro which updates the date values everyday in the list, to compare data between different dates.

    Lets say current date in validation cell B7 is 22nd of july, when i update the dates the latest as of today should be 23rd of July.

    However by default the date on B7 is still 22nd of July unchanged, so now what I need is a macro that will select the latest date on that cell from the cell validation list.

    Thanks.
    Last edited by rocksan; 07-22-2012 at 11:05 PM. Reason: word sequence

  2. #2
    Forum Contributor
    Join Date
    07-04-2012
    Location
    Al Khor, Qatar
    MS-Off Ver
    Excel 2013
    Posts
    101

    Re: Macro to to select latest date in cell with validation list consisting of dates.

    @ts i suggest to attached a workbook.

  3. #3
    Registered User
    Join Date
    04-05-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: Macro to to select latest date in cell with validation list consisting of dates.

    Quote Originally Posted by tangangtanga View Post
    @ts i suggest to attached a workbook.
    Okay, Ive attached a sample workbook.

    problemsample.xlsx

    In the workbook, sheet one has three cells A1,B1 and C1 with data validation consisting of dates, which refers to an array in sheet 2. I just want a macro that will set the A1, B1 and C1 to the latest dates, as new dates are added into the cell validation array in sheet 2.

    Thanks.

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Macro to to select latest date in cell with validation list consisting of dates.

    Pl try following formulas for
    A1,B1,C1 cells in sheet1.

    =MAX(Sheet2!1:1)
    =MAX(Sheet2!2:2)
    =MAX(Sheet2!3:3)

  5. #5
    Registered User
    Join Date
    04-05-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007
    Posts
    88

    Cool Re: Macro to to select latest date in cell with validation list consisting of dates.

    Ah, The obvious and succint answer I was looking for, converted the formula into VBA and it works perfectly.

    Thank you very much kvsrinivasamurthy.
    Last edited by Cutter; 07-23-2012 at 11:11 AM. Reason: Removed whole post quote

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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