Search for name across sheets and return most recent date

[ccarr1025]

I'm creating a sheet for my church. They would like to keep up with who has done what for each Sunday and Wednesday service.

I will have a file with a different sheet for each Sunday and Wed. (named after the dates at the moment) april 10, april 13, april 17 etc.

I have created drop down lists for them to select the member who has done something, but they also want a summary sheet that will show the most recent date someone has been used.

How could I look up a name from the summary sheet, compare it across multiple sheets (each name may pop up on multiple sheets) and return the date from the sheet that is most recent for that member?

I'm new to these forums and if someone has already had a similar question (looked bud didn't see one) then I apologize for crowding the board.

[bentleybob]

Welcome to the forum!

Can you post the file (with private data deleted)? Easier to work an example than to create one and have you figure out how to incorporate it into yours.

[ccarr1025]

Involvement Final.xlsx

I think this is how I'm supposed to attach a file here...

OK, I have an involvement sheet which lists who is willing to do what. I have a summary sheet where they want to see the most recent date someone served. I then have a few sheets for each Sunday and Wednesday service where I have made drop down lists for each participation spot based on who is willing to do them (from involvement sheet).

I just need a formula on summary sheet column B that will look for each name and return the most recent date they worked. I have no problem moving things around if needed.

thank you for any help.

[bentleybob]

Here's one approach.. All it takes is a simple lookup table in each daily tab.

Modify the "Template" tab as needed, leaving the lookup table intact (you can even hide the two columns). Then use the Template to create each new day's tab. (you can even automate this using VBA so all you need to do is to hit a button, identify the date you want, and it would create a new tab for you).

[ccarr1025]

Bentley, I really appreciate the help, and that does exactly what I needed it to do. Now though, I'm being asked to return what they did on that most recent date. I tried for a while to get that working, but couldn't get it set up.

again, any help is greatly appreciated.

[bentleybob]

Ok, how's this? I also noticed that you have a different format for Sundays and Wednesdays, so created two templates instead of one. Please note that there's a Lookup tab that simply has a table to convert from the number of the month (that Excel returns in a separate lookup) to the month name, which you use as the basis for your worksheet names. I hid the tab just to get it out of the way.

[ccarr1025]

Wow Bently, I really appreciate it, you're a life saver... and one heck of an excel guru in my book.

[bentleybob]

Happy to help!