Cross Sheet Formula(s) w/ Yes, No, and Hold Decisions (IF & AND)

Drew Frazelle

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.

Jeff M.

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", "")))