Update Existing Main Sheet Field from Data That is Entered on Another Update Sheet

Peter Tiedemann

I've looked through the forums and docs and haven't found an answer yet.

I think it could possibly be done using COLLECT, JOIN and MAX using certain criteria to match on. Perhaps someone has found a solution to a similar scenario.

We have a main sheet (Unit Tracker) where we collect all data related to manufactured units that are assigned unique serial numbers (SN). As each unit goes through the manufacturing process we want to add the most recent update notes in a column called "Latest Status". The status updates are collected on another sheet called "Unit Status Updates" in a column called "Status Update" - this sheet also has the SN of the unit as well as the date that the update was added. As soon as a Status Update is added, I would like to add (ie. copy) the "Date" plus the "Status Update" to the main sheet into the "Latest Status" field on the row that matches the SN, replacing the entry that was there previously. I don't think a Workflow can be created that does something as specific as this.

VLOOKUP can't be used because there will be multiple entries (rows) for each SN. Only the most recent entry needs to be transferred to the main sheet (perhaps using MAX).

Any help or suggestions would be appreciated.

Kelly Moore

Hey Peter

One solution is using a MAX/COLLECT and an INDEX/COLLECT for the two separate pieces of data you want to pull.

We'll first pull the Max Date from your "Unit Status Updates" sheet into your "Unit Tracker" sheet. You're right that the Max Date will give you the most recent Status Update date. This formula will go into your "Date" column on your "Unit Tracker" sheet. Make sure this column is formatted as a date column.

=MAX(COLLECT({Unit Status Update Sheet Date Updated}, {Unit Status Update Sheet SN}, SN@row))

In the "Unit Tracker" Latest Status column, we'll use the SN as well as the Max Date we just collected to grab the latest Status Update from your "Unit Status Updates" sheet.

=INDEX(COLLECT({Unit Status Update Sheet UPDATE}, {Unit Status Update Sheet SN}, SN@row, {Unit Status Update Sheet Date Updated}, [Max Date]@row), 1)

The '1' in the above formula is part of the INDEX formula and refers to the row-index. Remember you'll have to create each cross-reference range by clicking the blue link in the formula window and change any also column names to match yours.

I hope this helps,

Kelly

Kelly Moore

Hey Peter

One solution is using a MAX/COLLECT and an INDEX/COLLECT for the two separate pieces of data you want to pull.

We'll first pull the Max Date from your "Unit Status Updates" sheet into your "Unit Tracker" sheet. You're right that the Max Date will give you the most recent Status Update date. This formula will go into your "Date" column on your "Unit Tracker" sheet. Make sure this column is formatted as a date column.

=MAX(COLLECT({Unit Status Update Sheet Date Updated}, {Unit Status Update Sheet SN}, SN@row))

In the "Unit Tracker" Latest Status column, we'll use the SN as well as the Max Date we just collected to grab the latest Status Update from your "Unit Status Updates" sheet.

=INDEX(COLLECT({Unit Status Update Sheet UPDATE}, {Unit Status Update Sheet SN}, SN@row, {Unit Status Update Sheet Date Updated}, [Max Date]@row), 1)

The '1' in the above formula is part of the INDEX formula and refers to the row-index. Remember you'll have to create each cross-reference range by clicking the blue link in the formula window and change any also column names to match yours.

I hope this helps,

Kelly

Peter Tiedemann

Hey Kelly,

Many thanks for your solution! It worked perfectly for what I was trying to do.

Peter