Trying to pull most recent information from another sheet

kbegin

Hello! I'm trying to pull the most recent data from a reference sheet and my formulas just aren't working. My first column needs to find the matching EID and then pull the most recent date (since the same EID can have multiple test dates, but I want the most recent). Once I can figure this out, I'll need to pull the correlating results (i.e. passing result, tester, etc.) from that most recent-dated row.

This is one of the formula renditions I've tried using. I can't figure out what I'm doing wrong.

=IF([Fit Test Required?]@row = "Not Required", "Not Required", IFERROR(MAX(COLLECT({APD IP Fit Test Results- TEST NEW Range 5}, {APD IP Fit Test Results- TEST NEW Range 6}, EID@row)), "No Record on File"))

I also tried sorting in the sheet but that doesn't stick. i was told to build a report but i can't referent a report in the formula. any help would be appreciated.

khasimbashashaik

Hey@kbegin,
If you're looking for the most recent test date for a specific EID, your formula should work provided that

=IF([Fit Test Required?]@row = "Not Required", "Not Required",
IFERROR(MAX(COLLECT({Test Date}, {EID}, EID@row)), "No Record on File").

Pleaser feel free to reach me if you need any support. Thanks