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

Please Login or Register  to view this content.

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.