Find out who DIDN'T filled in the form based of team data

Bastiaan_Slot · ‎Mar 15, 2022

We own a company where we have a Done for you team member placement service and we want to track if they have filled in their End of the Day report every day.

Right now I have:

1 sheet where we collect our team members names
1 sheet where we collect the EOD reports

I would love to automate the manual work of finding out who DIDN’T filled in the EOD report by the end of the day (11:45PM) so I can follow up with them the day afterwards.

I tried the automation, but I get a bit stuck here. Can someone help? :slightly_smiling_face:

kuovonne · ‎Mar 15, 2022

You need more than just an automation. You need some roll ups and formulas to support the automation. This is assuming that the two sheets/tabs/tables are linked together.

Have a rollup in the [team members] table that identifies the latest EOD report. Use MAX(values) in the rollup formula. Have a formula field that finds the difference between TODAY() and the latest EOD report. Then trigger off that difference. You may also need to adjust the formulas so that they will reset every day, or the automation will trigger only for the first missed EOD report, and not for consecutive missing reports.