Can automations involve removing a row from one sheet when certain criteria are made on another?
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?
Best Answer
-
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.
Answers
-
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.
-
This is amazing! Thank you so much!
