Site Visits

Hi Everyone,

From my point of view this is very complicated to work out so i will try and simplify it as small as possible.

People visit site locations on a monthly basis. I want to show on a smartsheet dashboard what sites need to be visited when there is only 1 visit on the site and that date is about to run out of the 30 day rolling period.

This is a photo of where the data is collected and you can see there are repetition on sites within an area of time. This can be called Sheet 1

In Sheet 2 is where all of that repetitive data gets collected within a 30 day period.

What i would like to happen is that when a location such as Aberdeen - Westhill has 1 site visit and that visit is 23 (out of 30) days old to put into a new cell somewhere to visit Aberdeen - Westhill.

The final goal is to be able to display this automatically on a Smartsheet Dashboard when a site is about to go to 0 site visits within the last rolling 30 days.

If anymore information is needed reply to this post and I will reply back as quickly as possible.


  • KPH
    KPH ✭✭✭✭✭✭

    I have made some assumptions, let me know if any are incorrect.

    Sheet 1 contains multiple rows for each location with a date for each. For the purpose of illustration, it is something like this:

    Sheet 2 contains one row per location and a count of the the number of visit dates within a time frame. This is probably created using a formula similar to: =COUNTIFS({Site Location}, [Site Location]@row, {Visit Date}, >=TODAY(-30))

    It looks something like this:

    You would like something to add to a dashboard that shows a list of locations where the last visit date is not within the last 23 days.

    To do this, I would add Latest Visit Date to sheet 2:

    This uses a MAX COLLECT formula, like:

    =MAX(COLLECT({Visit Date}, {Site Location}, [Site Location]@row))

    (your cross sheet references will differ)

    Then create a row report from this sheet, that displays only the rows where Latest Visit Date is not within the last 23 days. The result is:

    The filter is:

    You can also add additional filters to only include those where the visit count is 1 (as you mention) but this would risk excluding any that have not been visited and at all and any that had 2 visits in the last 30 days but both were more than 23 days ago.

    I also sort this by date so the most worrisome is top.

    Hope this does what you need.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!