Can automations involve removing a row from one sheet when certain criteria are made on another?

KarenTF

Here is what I'm needing and maybe someone can give a better suggestion on how to accomplish this. I have a sheet that is being populated with data each month from 60 different sites. What I would like to do is create a separate site sheet that just has all sites listed and when the main data sheet gets populated, the site on the site sheet will automatically get removed, that way I can have a quick at-a-glance list of who all has not yet submitted their data for that month. The end idea is to be able to automate a daily request at a certain date to the remaining sites who have not submitted for the month. Any suggestions?

Paul Newcome

It won't be able to work exactly how you are thinking with automations, but you can use a list of all sites with a flag type column and a formula to flag each site that has not yet submitted for the month.

=IF(COUNTIFS({Source Sheet Site Column}, @cell = [Site Column]@row, {Source Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))) = 0, 1)

Then you can set up your automation in this master list with the flags to send an alert to each site on a specific day of the month but only on rows where the flag is present when it runs that day.

If you wanted to have the "at a glance" view, you can apply a filter to the sheet or create a report to show only rows that are flagged.

Paul Newcome

It won't be able to work exactly how you are thinking with automations, but you can use a list of all sites with a flag type column and a formula to flag each site that has not yet submitted for the month.

=IF(COUNTIFS({Source Sheet Site Column}, @cell = [Site Column]@row, {Source Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))) = 0, 1)

Then you can set up your automation in this master list with the flags to send an alert to each site on a specific day of the month but only on rows where the flag is present when it runs that day.

If you wanted to have the "at a glance" view, you can apply a filter to the sheet or create a report to show only rows that are flagged.

KarenTF

This is amazing! Thank you so much!

Paul Newcome

Happy to help. 👍️