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!
-
Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives