Return cell value from cross sheet reference under certain conditions

We are using smartsheets as a yard management system and we have a unique workspace for a particular type of shipment. I also have a Master Sheet that contains ALL trailers that have come to the facility and are currently at the facility. When a trailer number is added to the shipment in the unique workspace, I want to pull certain information over to our master database of all trailers.

This is the formula I was using to pull in the load contents: =IFERROR(((INDEX(COLLECT({Load Contents}, {Trailer #}, [Trailer #]@row, {Shipment ID}, [Shipment ID #]@row), 1))), " ")

The formula works, but the issue I am running into is that the same trailer may come to the facility numerous times - days or weeks apart. So whenever the trailer number appears on the master sheet, it auto-populates the load contents, even if that trailer has not been loaded this visit.

If the status on the master sheet is shipped or the status in the unique workspace is shipped, I don't want the cell information for the load contents to change from what it already says.

Any ideas on how to populate the information from the sheet in the unique workspace to the matching trailer in the master sheet for only the active trailers?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!