Locating the latest date in multiple worksheets within a workbook.

I have a workbook that contains 12 worksheets (Jan08 to Dec08).

In each worksheet there are many columns but the ones that matter to this question are column A which contains the Location Name and Column C which contains the Date Collected.

Each Location may be collected more than once in the month, thereby creating multiple rows with different dates. Sometimes a locationn will not be collected in a specific month and the cell in Column C will be blank.

I am trying to create an Overview sheet which will report the latest date that each location was collected and report it in a "Latest Date Collected" column on the overview sheet.

I have tried various methods ... which seem to be able to get the latest date but only for the entire column and does not take into consideration the Location names.

The Overview sheet has to contain the same information in Columns A & C of the all the worksheets (ie: Location Name and Date).

I've tried this:

in every month sheet - X1: =LOOKUP(9.99999999999999+307E,C:C)

and - Y1: =IF(ISNA(x1),"NA",X1)

Then in C2 of the Overview sheet (which is the first row of information in Date column): = MAX('Jan08:Dec08'!Y1)

All this does is give me the latest date for Column C for all worksheets. It also stops at the blank cells and doesn't look any further.

So ... in a nutshell .... I need to get Excel to look at each worksheet, locate all the times the specific location is listed, grab the latest date after doing that from all the worksheets, and then insert it into the Oversview sheet in the Date column beside the Location Name. Is this possible?

Kathy