Match Two Entries from Two Separate Form Sheets, Assign Status

Chris Sams
edited 12/09/19 in Formulas and Functions

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!