Return Max Text or Number Value based on Criteria

[kwilson307]

Hello,

I have a workbook with multiple sheets. I am trying to have my totals page populate newest information based on criteria from another sheet.

Sheet 1: Totals.

Tech#(A1)|R22 Drum# (B1)|Date Assigned (C1) | PO# (D1)| Date Signed Out (E1)


3202 (A2)
3225 (A3)
3236 (A4)

Sheet 2: R22_Batch. This is the sheet we enter person who has signed out a specific drum. The Drums contain letters (i.e. Z56)

Tech#(A1)| R22 Drum#(B1)|Date Assigned (C1)|PO#(D1)| Date Signed Out(E1)

Sheets 3, 4 & 5 are the same, just a different Drum Types (410-A, PD, & Unique number values.

I need Sheet 1 to reference Sheets 2-5 and populate the newest information as it is updated automatically. The drums are not assigned in order. We may sign out Z01, then Z07, then Z25 . We do have the drums pre-listed in order. I have attached a small sample for visual.

What is the best way to achieve this?