Match Two Entries from Two Separate Form Sheets, Assign Status

08/14/18 Edited 12/09/19

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Try this...

     

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

    thinkspi.com

  • Paul Newcome,

    You solved it!

    Thanks,

    Chris

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Excellent! Happy to help.

    thinkspi.com

Sign In or Register to comment.