Return date last attended (colum header) from multiple worksheets

[M4rk]

Hi Guru's

I'm working on a file that contains a Membership List, Volunteer List, and individual monthly attendance sheets, all as tabbed worksheets.

The attendance sheet column 1 contains various activities grouped together (such as After School Club or Saturday Club). The column is prefilled with 10 - 50 entries based on our historical attendance figure (allowing for some growth). ie col 1 rows 3 - 49 = After School Club and col 1 rows 50 - 60 = After School Club Volunteers and so on.

Column 2 row 2 thru column AF row 2 (column headers) shows each day of the month (see screenshot attached) Attendance.jpg

From Col 2 to Col AF and rows 3 downwards names are selected from a validation list obtained from the Name column in the Membership (or Volunteer) tab. (see attached) membership.jpg

Now, I have managed to automatically update the total number of attendances per member (col S, Membership tab) over the course of the year using the

=COUNTIF(Sep!$B$3:$AE$271,[@Name])+COUNTIF(Oct!$B$3:$AE$271,[@Name])

including all additional monthly worksheets but I also want to show the last date each member attended in the next column (col T, Membership tab).

Any ideas how I can get the last date each member attended any one of our activities or am I barking mad? Hope I've explained it sufficiently

Thanks, Mark

[M4rk]

I'm guessing my original post was not clear enough or the images of the pages did not help my requirement. I have uploaded the xls to Skydrive http://sdrv.ms/Rel52Q which may help anyone understand what I'm looking for.

[M4rk]

[SOLVED] I had cross posted this on the MrExcel forum and have now received a formula that solved my problem.

I have now had chance to test this and can confirm that the solution provided by Andrew has indeed solved my problem. I took his suggestion of creating additional columns (AZ - BJ) for each month and ran the following array formula (modified for each month) to return the last date attended as follows {=MAX(IF(Sep!$B$3:$AF$5000=A3,Sep!$B$2:$AF$2))}. I then used another formula to return the latest attendance date for each member as follows: =IF(ISBLANK(A3),"",MAX(AZ3:BJ3)).

Thanks again for your help.