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?
Answers
-
Can you use the status as an IF in front of the index collect. SO IF([status]@row="Shipped","",Index(Collect....
-
@markkrebs I tried something similar to that, but then the information cleared out of the cell once the status was changed to shipped.
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!