Site Visits

Archie123456

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

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.