Automation help or formula for connecting rows

Casie

AW Placement request form below takes in all of the school placement requests via a form.

Once the column AW REQUEST STATUS is 'approved by leader' it is automatically moved to Students/Instructors - AWI

The ANCHOR ID transfers over from AW placement request sheet to students\instructors - AWI - when approved by leader status is in AW Request Status

Once the request is on the student/instructor - AWI request status changes to Upload student/instructor info.

This is where I am struggling. In the picture below there is Row 1 = the placement request moved from AW placement request sheet. Row 2-4 = school has uploaded students (3 students in this case) (step 1)

Row 5-7 = all students have completed their intake form (step 2)

Rows 8-10 = all students have completed onboarding requirements. (step 3)

Issue: when step 1 is not complete I have an automation sending a reminder to the school to upload students but I'm not sure how to trigger this as rows 2-4 don't talk to row 1 and so forth for 5-7 and 8-10.

Is there some sort of formula or automation that can alert when step 2 is not complete and when it is then when step 3 is not complete and times if by the number of students attached to the request?

dojones

You can trigger based on When a date is reached. Repeat every week, every week day at 7am as an example. It will run every day at 7am. Use conditions to determine when it should send someone an alert.

I suggest making every row a student ID. Then you can evaluate each student on each row as to whether the steps are completed for that student.

Something like this. If you want summary counts by Request ID, then it would require another sheet