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?

Attached Files

Ref_Batch_Log_Forum_V1.xlsx (14.9 KB, 11 views)

For example, for R22_Batch, start by getting latest date...

So in column E2 try array formula:

=MAX(IF('R22_BATCH'!$A$2:$A$14=A2,'R22_BATCH'!$E$2 :$E$14))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

You may have to format as date... use custom format and enter type as: m/d/yyyy;-0;;@

This will get rid of the 0 dates values.

Then you can get the matching data...

in B2, another array formula:

=IFERROR(INDEX('R22_BATCH'!$B$2:$B$14,MATCH(1,('R2 2_BATCH'!$A$2:$A$14=A2)*('R22_BATCH'!$E$2:$E$14=E2 ),0)),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

Similarly you can get the info for the other columns...

Then repeat the steps for the other sheets.

Hope this helps.