Help with MAX date with one criteria, from another sheet, and have it look at generated text

[MortifiedPenguin]

Hi guys,

first post here, and i used the search function extensively with no luck.

I need the LATEST date, inside of a column of another sheet that matches a specific name.

It's a communication log tracker. so on one page, i pick the name Mr. Smith, and then click a macro which generates on that same row, the timestamp of the call.

On the NEXT sheet, i need to be able to have Mr. Smith have one line, and have a formula for a column labeled as "most recent call". I would be choosing his name from a drop down, if that makes any difference.

My problems: I can get the same IDEA on the same page, but only with values, and not dates. When i try to link the sheets with =MAX(if and =Large(if type arrays, it never works. generates the dates as 1/0/1900, or gives me an error about not having enough variables.

Any help is GREATLY appreciated, thank you!!!

 

[Sinon]

Not sure if this will work as I am unsure about where your data is:
Assuming that you have the names of the callers (like Mr Smith) in Sheet1 column A and their timestamps in column B
{=MAX(IF(Sheet1!A:A=Sheet2!A1,Sheet1!B:B))}
Where A1 on Sheet 2 is your dropdown cell where you select Mr Smith.

Do not add the curly braces. Instead, confirm the formula with CTRL+SHIFT+ENTER, not just ENTER
Adjust for your own range and format the cell you put this formula in by selecting it, press CTRL+1, go to Custom and put this in: dd/mm/yyyy hh:mm:ss (or if you need the American format change the day with the month)

 

[XOR LX]

=MAX(IF(Sheet1!A:A=Sheet2!A1,Sheet1!B:B))

Not at all a good idea to reference entire columns within an array formula.

If, for example, you only have data extending as far as row 1000, then, by referencing an entire column's worth of rows, you are effectively forcing Excel to calculate more than one million rows than are actually necessary, resulting in an astonishingly resource-heavy formula. And that's just for one instance of that formula.

Regards

 

[Sinon]

Which is why i said "Adjust for your own range" in my post.

 

[MortifiedPenguin]

Hey guys i think i realized maybe what the problem is. I followed that exactly, and am still getting "00/01/1900 00:00:00"

this is what the formula is calling it when i click on the name on sheet 2, which is pulled from a table
"=MAX(IF('Communication Logs'!D3:D400=[@Doctor],'Communication Logs'!F3:F400))"

The doctor's name in Sheet 2 is in a table that i got from a template. sorry for the confusion. When I clicked on the cell to input that cell into the formula, it put in the [@Doctor]. I tried to figure out how to make the formula JUST look at the text in the cell, but I couldnt get it to work.

Thanks so much again for your help, I need this up and running asap!!

 

[MortifiedPenguin]

Any thoughts?