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.
Answers
-
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
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
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!