Match Two Entries from Two Separate Form Sheets, Assign Status

Chris Sams

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?

 

Paul Newcome

Try this...

 

=IFERROR(IF(MATCH([Daily Assignment Number]1, {Form: Daily Production Range 1}, 0) > 0, "Green"), "Red")

Chris Sams

Paul Newcome,

You solved it!

Thanks,

Chris

Paul Newcome

Excellent! Happy to help.