Return cell value from cross sheet reference under certain conditions

Ashley F

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?

markkrebs

Can you use the status as an IF in front of the index collect. SO IF([status]@row="Shipped","",Index(Collect....

Ashley F

@markkrebs I tried something similar to that, but then the information cleared out of the cell once the status was changed to shipped.

markkrebs

Id have to look at the sheet structure a little more. are there 3 sheets? Trailer Database sheet, load contents sheet and a shipment sheet? What uniquely identifies the load contents? We can setup a quick call if you'd like