Max/If statement across multiple worksheets not working

Hi All,

I have a very basic knowledge of excel but as I know where the power button on a pc is work seems to think I'm a computer genius.
I have been tasked with setting up something which shows if someone has not been on a certain job for a while (basically a rota).

For this I was hoping to set up a spreadsheet that looks across multiple sheets and brings back the most recent date only when someones name is in another field.

I have this so far:

This works
=IF(First!F2=Sheet2!A2,(MAX(First:Last!B2)),"no answer")


This doesn’t
=IF(First:Last!F2=Sheet2!A2,(MAX(First:Last!B2)),"no answer")

F2 is where I put the person on the "rota", A2 is the the list of names, B2 is the date.

Problem with the first one is the IF part of the statement only looks at one worksheet and not the range as desired.

Any help would be appreciated but my understanding of excel is not great.
There is no sheet to upload as haven't really made one as cant get the basic principal to work.

Thanks for your help in advance

M

Very basic idea
first page would have a list of names that have dates for each role.
Could then create a separate sheet that gets data from this.

Not a lot of help I know.

M

The first tab I would use as the reference for each operator. It would be a list of about 50 names with about 20 different jobs along the top. The cells would then contain the date last on that job.

I was then hoping to use all the other tabs to record where people where on that day and print off for people to see where they where going.

Thanks for the help.

Forgot to say the first tab would get the dates from the most recent dates on the tabs.

Thanks for the input.
That only works if the persons name is always in the same cell.
Also there are only a few tabs currently but could end being alot as the year goes on. This is why I tried using ranges allowing for new sheets to be added.

Thanks again though.

They aren't really necessary. They where just thinking of making it simpler to fill in. Hiding the other cells with the dates just to make it look cleaner.

Basically just need an equation that searches all tabs for a name and returns the highest value for the cell next to it on those tabs.

If anyone can help I can then fit everything else around it.