Finding the minimum value of maximum values from multiple, changing worksheets

Hi!

I am looking to find the minimum date after comparing multiple worksheets' maximum dates. In other words, if I have multiple sheets listing update dates, I want a cell to output the least recent update date and which sheet was least recently updated.

Some caveats are that the update dates will be constantly changing (thus the row of the most recent update for each sheet will as well), and there may be new sheets added. I also want to ignore date from sheets whose tab is colored purple or black (color index 29 and 1).

I have a "summary sheet" that has all of the update dates next to the sheet name, excluding the purple and black sheets.

So far, I have tried multiple formulas, taking the minimum of the maximum of the multiple sheets, but this does not account for the dynamic nature of the workbook. I have also tried to think about take the minimum of all the last cells in row A, looping through the active sheets, but I do not know how to do this in VBA.

Any help would be very much appreciated! I've attached an example Excel workbook to clarify!
Example Minimum Date.xlsx

Hi! Thanks for your reply. I'm not sure I see what your code is doing though. I have followed the instructions and nothing happens. Could you explain more? Thanks!

Thank you so so so much!! I modified the code a little bit for what I was looking for, but I couldn't have gotten anywhere near the answer without you! Thanks!!

Please Login or Register  to view this content.

Hi again!

I just ran into a glitch - I actually need the least recent date where all of the columns have been filled in. I included an updated version of the example sheet; where it says "11/5/14" now, it should say "11/4/14" because there is a blank in the 11/5/14 column on the third sheet. I hope that makes sense! And sorry, this was my fault for not including more columns in the example sheet in the first place!

Thanks!
Kelleigh

Example Minimum Date.xlsm

Also, if it helps, I have this function stored in a Module in VBA:

Please Login or Register  to view this content.