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.

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

Hello Glenn,

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

Hawkmoth1

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.

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

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

Hawkmoth1

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.

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