Update Existing Main Sheet Field from Data That is Entered on Another Update Sheet
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.
Help Article Resources
Check out the Formula Handbook template!