Cross Sheet Formula(s) w/ Yes, No, and Hold Decisions (IF & AND)
Hello All,
I am trying to utilize a formula from a sheet (Parts Requests) to drive productivity and scheduling on another (Scheduling Board). I have created a formula [Report] to determine if the corresponding materials order has been received, on order, shipped, etc. which corresponds to a Yes, Hold, or No decision icon.
My question is, how do I formulate this on the productivity sheet? I want the {Scheduling Board} to reference my {Parts Requests} by the corresponding [Work Order]. Collect the [Report] data (maybe multiple PO numbers) and send a corresponding Yes, Hold, or No for the following logic/example:
PO1234, 5678, 9012 are ordered for WO2970
1) 2/3 POs are received (Not all POs are received, returns a "Hold" icon to the [Parts] column of the Scheduling Board).
I had thought to use an INDEX/MATCH but couldn't piece it together nor figure out how to formulate an "ALL" not "SOME" response.
I tried a JOIN/COLLECT and the Decision balls cannot handle multiple values.
(Decision balls pictured above were placed just for reference)
Thank you in advance.
Best Answer
-
Try creating a helper column "collect reports" in the second sheet next to the parts column and try using this method..
=JOIN(COLLECT({Report Range 1}, {Work Order Range 1}, CONTAINS([R/O #]@row, @cell)), " - ") - This should collect and combine every status from matching work orders. It should collect the words rather than the symbols.
Then in the Parts column that you are working on, try this method..
=IF(CONTAINS("Hold", [collect reports]@row), "Hold", IF(CONTAINS("No", [collect reports]@row), "Hold", IF(CONTAINS("Yes", [collect reports]@row), "Yes", "")))
Answers
-
Try creating a helper column "collect reports" in the second sheet next to the parts column and try using this method..
=JOIN(COLLECT({Report Range 1}, {Work Order Range 1}, CONTAINS([R/O #]@row, @cell)), " - ") - This should collect and combine every status from matching work orders. It should collect the words rather than the symbols.
Then in the Parts column that you are working on, try this method..
=IF(CONTAINS("Hold", [collect reports]@row), "Hold", IF(CONTAINS("No", [collect reports]@row), "Hold", IF(CONTAINS("Yes", [collect reports]@row), "Yes", "")))
-
Sorry for the late response to your suggestion. The creation of the helper column was what I needed. Thank you for your assistance.
~ Drew
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!