Match Two Entries from Two Separate Form Sheets, Assign Status
I have an "Assignment" form that creates a system generated number. The crew foreman will use this form for their assignment, then they will populate a "Production" form using the system generated number from the "Assignment" form to reflect the production done for that assignment.
I'd like have a RYG ball on the "Assignment" sheet to reflect if a matching number was entered in "Production" form sheet. I used the following formula to generate the RYG ball. ("Red" and "Green", only, actually)
=IF((MATCH([Daily Assignment Number]1, {Form: Daily Production Range 1}, 0)), "Green", "Red")
However, I am only getting the green ball for the latest entry in the "Assignment" sheet that has a match in the "Production" sheet. The older "Assignment" form rows are returning "#INVALID DATA TYPE", and the "Assignments" form rows without corresponding "Production" forms return a value of "#NO MATCH".
Is their a better way of coding this formula?
How can I get the "Assignment" row to show a "Red" ball if a "Production" form has not been created matching the "Assignment" system generated number?
How can I get the "Assignment" row to show a "Green" ball if a "Production" form has been created matching the "Assignment" system generated number?
Comments
-
Try this...
=IFERROR(IF(MATCH([Daily Assignment Number]1, {Form: Daily Production Range 1}, 0) > 0, "Green"), "Red")
-
Paul Newcome,
You solved it!
Thanks,
Chris
-
Excellent! Happy to help.
Help Article Resources
Categories
Check out the Formula Handbook template!