Submission checklist to reference production schedule

Mark.Hendley

Good morning,

I have 2 Sheets

1. Master Production Schedule - consists of multiple columns, Job #, Job Name, Line 2 Count, Forms Submitted?, and Status
2. Check List Submission - consists of multiple columns, Job #, Job Name, Line 2 Count, Submitted, Outstanding Entries, Missing Form Override(Checkbox), and Status

The idea is that when our line 2 production workers are in the final stages of quality they need to fill out a paper form and then upload some info and a photo of the paper form into the Smartsheet Check List Submission, The Line 2 count will reference the same column on the Master Production Schedule, the Submitted column will just count however many entries have the same Job #, Outstanding entries will just be Line 2 Count - Submitted.

* I believe I have mostly everything figured out, where I am struggling is if the job on the Master Production Schedule is marked off as complete in the Status column and there are still Outstanding entries on the check list submission I need the Status column (Check List Submission) and the Forms Submitted? column (Master Production Schedule) to both say "Missing Forms".

Automation will be set up as follows (Check List Submission)

If Status = Complete, move rows to Archive Sheet

If Status = Missing Forms, notify Production Supervisor

If Status = Overridden, move rows to Archive Sheet and notify Production Supervisor

I hope this is clear as to what I need help with, thank you for your time.

Brian_Richardson

On your Master Production schedule sheet, add a column for Total Outstanding Entries with the formula:

= SUMIF({Job #}, [Job #]@row, {Outstanding Entries})

The {} references are created when typing the formula by clicking Reference Another Sheet, browse to the Check List Submission sheet, and click to select the whole column

Once you enter the formula, right click it and choose Convert to Column Formula.

That will give you a sum of all the Outstanding Entries from the Check List Submission sheet where the Job # matches. You can then check for that number to be 0 in formulas (like for Forms Submitted?) and in automations (like for Status).

For the Check List Submissions Status column, it sounds to me like you just need to have a formula like =IF([Outstanding Entries]@row>0,"Missing Forms")

Mark.Hendley

@Brian_Richardson I appreciate your help! I guess I forgot to mention something, when the job on the master production schedule isn't yet complete and all the forms have not yet submitted id like it to say "Pending Submissions". Only when the Job on the master production schedule is marked as complete and there are missing forms id like it to say "Missing Forms"

Essentially,

If job# matches between both sheets, outstanding entries is greater than 0, and the status is NOT "complete" show "Pending Submissions" BUT If job# matches between both sheets, outstanding entries is greater than 0, and the status on the master production schedule is "complete" then show "missing forms" on the status column in the check list submission sheet.

I'm just now sure how to put that into a formula

Brian_Richardson

You can do this with a couple of IF and AND statements in a formula on your Job Master sheet

=IF(AND(Total Outstanding Entries]@row>0, Status@row<>"Complete"), "Pending Submissions", IF(AND([Total Outstanding Entries]@row>0,Status@row="Complete"),"Missing Forms"))