Alerts for sites not completing a form

Cathi R

Hello, I am trying to find a way to do an Alert for a sheet that has the following scenario:

1. Form is filled out and added to sheet.
2. Each line item has a date column, Site Name column, etc.
3. I need to be able to automatically send an alert when a site does not appear on the sheet for a particular day.

Is this possible? Any ideas? I have been going crazy trying to figure this out. I can tell them when it's completed but not when it is not completed.

Thank you,

Cathi R

James Keuning

Let's call your sheet ACTUAL, and a utility sheet you will create called WORK.

I would create a scaffold in the WORK sheet, basically each date for the month, and each site. Depending on how many sites you have, it may or not be economical to build this for an entire year, or a month, or whatever. You might also be able to get creative and have this scaffold regenerate each month, but that's another question. The list will represent one day for each site. And then create a formula that combines SITE and DATE. Like this: Site@row + Date@row. Call it SITE_DATE. Create another column, call it ANSWER and populate it with something, you can make it a checkbox and use TRUE (use a column formula ="TRUE".

Now create the same SITE_DATE column in ACTUAL. And create a column called TEST. In this column you can use a VLOOKUP or INDEX/MATCH to find the corresponding record in WORK. Use the lookup to pull in the ANSWER field. Missing answers will fail the lookup.

Now create another column in ACTUAL with the formula:

=AND(Date@row < TODAY(), TEST@row = false). This will return FALSE for any date/site combos that are present in WORK and missing in ACTUAL.

Your alert can trigger off of the TRUE answers.