+ Reply to Thread
Results 1 to 13 of 13

Formula to return the 'earliest' and 'latest' dates from a list of dates is not working

  1. #1
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Formula to return the 'earliest' and 'latest' dates from a list of dates is not working

    Hello Excel specialists,

    As I’m sure many of you are aware (as I have used this forum a lot), I am in the process of building a recording program for moths and butterflies and on this occasion I have an issue as regards loading the ‘earliest’ and ‘latest’ dates on which a species of butterfly has been recorded.

    The problem is in cells G2542:H2649 the ‘index’ worksheet of the attached workbook, Column G should give the ‘earliest date’ recorded and column H should give the ‘latest date’ recorded. The source for these dates is in column K on the ‘Butterfly’ worksheet.
    As can be seen in cells G2541 and H2541 the formula for the earliest and latest dates does work right to the top of the sheet for both the ‘macro moths’ and the ‘micro moths’, but when I drag it down to get the same results for the butterflies, I just get an error ref and I just can’t work out how to fix it.

    I apologise in advance for sending the complete recording program, but stripping bits out to send you is not an easy task.

    For your information I am using Excel 2003 and Windows 10.

    Thanks for any help.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Formula to return the 'earliest' and 'latest' dates from a list of dates is not workin

    Perhaps because it trying to reference a sheet called "Butterfly moths" ( "Butterfly" in A2542?)
    Last edited by JohnTopley; 03-20-2017 at 09:56 AM.

  3. #3
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Formula to return the 'earliest' and 'latest' dates from a list of dates is not workin

    The root cause of your problem is in INDEX column A. If I am correct, you need a formula that categorises the species as 1) Micro Moths, 2) Macro Moths, or 3) Butterflies. that term can then be used in the INDIRECT formula to go to the correct sheet.

    is that correct????
    Glenn



  4. #4
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Formula to return the 'earliest' and 'latest' dates from a list of dates is not workin

    Hello John,

    Thanks for that, but can you please tell exactly what formula I need to enter in which cells to get a fix?

    Hawkmoth1

  5. #5
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Formula to return the 'earliest' and 'latest' dates from a list of dates is not workin

    Hello Glenn,

    Yes, what you've stated seem correct to me.

    Hawkmoth1

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Formula to return the 'earliest' and 'latest' dates from a list of dates is not workin

    As per Glenn's reply:

    =IF($A2542="","",DATE(INDEX(INDIRECT("'"&$A2542&" Moths'!V3:V12000"),MATCH(MIN(IF(INDIRECT("'"&$A2542&" Moths'!E3:E12000")=index!$E2542,INDIRECT("'"&$A2542&" Moths'!U3:U12000"))),INDIRECT("'"&$A2542&" Moths'!U3:U12000"),0)),1,MIN(IF(INDIRECT("'"&$A2542&" Moths'!E3:E12000")=index!$E2542,INDIRECT("'"&$A2542&" Moths'!U3:U12000")))))

    This formula needs now to test for "Micro Moths","Macro Moths" and now "Butterflies" (not Butterfly): it is going to be a bit "messy" (long) Unless you type the full text e.g "Micro Moths" in column A: the formula below will suffice.

    Please Login or Register  to view this content.

  7. #7
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Formula to return the 'earliest' and 'latest' dates from a list of dates is not workin

    Formulae in A, G and H of Index have been changed. You may need to adjust the ranges of the formulae in A...
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Formula to return the 'earliest' and 'latest' dates from a list of dates is not workin

    Hi John and Glenn - thanks very much for your help on this, all data is loading properly now - and Glenn, I have readjusted the ranges.

    Just before I mark this post as solved (and a reputation hit for both of you), could I please ask you whether it's possible to to change the options offered in the filter box of A2 on the 'Index' worksheet (without using code). Ideally I would like only the following options in the filter box to be available to the user:-

    All Species
    All Species with data
    Macro Moths
    Micro Moths
    Butterflies

    If this can't be done, my second option would be to ask you whether Sort Acceding, Sort descending, Top 10, Custom and blanks could be 'greyed out'.

    If you feel that this needs to be another question for the forum, I fully understand, thanks

    Hawkmoth1

  9. #9
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Formula to return the 'earliest' and 'latest' dates from a list of dates is not workin

    I don't think you can do that.. That's inbuilt into Excel... Unless JT or someone else knows differently...

  10. #10
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Formula to return the 'earliest' and 'latest' dates from a list of dates is not workin

    Thanks for your thoughts Glenn......does anyone else have any further ideas re my question in post 8, thanks?

    Hawkmoth1

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Formula to return the 'earliest' and 'latest' dates from a list of dates is not workin

    Re A2 filter the options:

    The "options" are determined by the content in column A so if all the categories you require are recorded in column A you will be able to filter. There is no way of specifying "All species with data" as I assume this means for example "Micro Moths with Data", "Macro moths with data" etc. as opposed to the simple "Micro Moths"

    The above might be possible using VBA.

    If you need to ignore blanks the you will need to specify the all individual categories.

    As for "greying" out: as per Glenn's reply it cannot be done.

  12. #12
    Forum Contributor
    Join Date
    01-07-2015
    Location
    Mid Wales, UK
    MS-Off Ver
    Office 2003
    Posts
    115

    Re: Formula to return the 'earliest' and 'latest' dates from a list of dates is not workin

    Thanks for your answer re filter options John, I thought it might be difficult (or impossible) to do.

    I will now mark this post as solved and hit both your reputations.

    Hawkmoth1

  13. #13
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Formula to return the 'earliest' and 'latest' dates from a list of dates is not workin

    You're welcome and thanks.

+ 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. [SOLVED] Return the earliest and latest dates selected from a list of dates.
    By Hawkmoth1 in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 02-01-2017, 09:41 AM
  2. Help with sorting dates from earliest to latest
    By probuddha in forum Excel General
    Replies: 11
    Last Post: 06-15-2015, 03:53 PM
  3. [SOLVED] Formula to return earliest and latest time.
    By Shellybelly in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-07-2014, 01:35 AM
  4. [SOLVED] Finding the Earliest and Latest Dates for each Task Code
    By BenTFleury in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-07-2014, 03:45 PM
  5. Formula - Determining Earliest and Latest dates for a Category of records
    By haleakala17 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-25-2014, 11:35 AM
  6. Earliest and Latest Dates from a list of names?
    By TimMatrix in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-21-2008, 07:34 PM
  7. [SOLVED] Return the latest date from a list of dates
    By davidp in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-17-2005, 09: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