Verify All Sites Submitted Response Today

Amy Arnold

Hi,

I'm trying to track if a site (or Name) created a new daily entry using a Smartsheet form.

I added this formula to the Updated Today column to return a "Yes" answer if the site (or Name) created a new daily entry. This seems to be working as intended. Is there a better way to design this formula?

=IF(Created@row = TODAY(), "Yes")

Then I added another formula to the green cells designated for Updated Today. This asks if all sites (or Name) had a "Yes" in the Updated Today column. This seems to be working as intended. Is there a better way to design this formula?

=IF(HAS([Updated Today?]1:[Updated Today?]8, "Yes"), "Yes")

I also need another formula to ask if a new entry was created the day before for all sites (or Name). Suggestions?

Thank you in advance.

Genevieve P.

Hi Amy,

Your first formula looks great! However your second formula will return a "Yes" even if only one of those rows contains a "Yes", is that what you were looking to do?

If you only want a "Yes" to appear once all of the 8 cells contain a "Yes", you could nest a COUNTIF statement within an IF statement to check all 8 cells:

=IF(COUNTIF([Updated Today?]1:[Updated Today?]8, "Yes") = 8, "Yes")

This counts all of the Updated Today cells that say "Yes". If there are exactly 8 of them, then your IF formula will return Yes. If there are any less than 8, the cell will be left blank.

In regards to checking if the updates were Yesterday, you can use that same COUNTIFS formula but to check and see if the dates in all 8 cells are yesterday:

=IF(COUNTIF(Created1:Created8, =TODAY(-1)) = 8, "Yes")

This will return a Yes if all 8 dates in the Created column are the day before today, or Today minus 1.

Let me know if you have any questions about the above formulas!

Cheers,

Genevieve