Sheet Summary NETDAYS referencing another sheet summary formula
Hi,
I cannot quite find a solution I require for this question in the community. Hopefully someone with more knowledge can help.
I have H&S sheet logging any incidents, accidents, near misses etc. I am trying to build good sheet summary data to pull metrics through to a dashboard.
I have already completed Date of Last "X" and Days Since Last "X" based on the previous summary using MAX(COLLECT for date and "accident" I have then referenced that summary formula in a NETDAYS formula to count days since last "accident" (see image).
However, we have multiple sites and projects and I need a way to pull these metrics for individual sites in a single sheet. For example, in the summary, I would need metrics for "Factory 1", "Factory 2" etc.
I cannot make a multiple criteria MAX(COLLECT work to based it on last date, site and type of incident. I have been struggling to figure it out for the last couple of days.
I have:
=MAX(COLLECT([Date Raised]:[Date Raised], [Accident, Incident or Near Miss]:[Accident, Incident or Near Miss], CONTAINS("Accident", @cell)))
Followed by:
=NETDAYS([Date of Last Accident All Sites]#, TODAY())
Can someone please help me find a solution?
Thanks!
Best Answer
-
Hi @Mick Tim
The COLLECT function should allow you to enter a new range (column) and criteria to make it have multiple criteria.
Structure example:
=MAX(COLLECT([Column to MAX]:[Column to MAX], [First Column]:[First Column], "First Criteria", [Second Column]:[Second Column], "Second Criteria"))
This means you should be able to add on to your working formula by listing the Site column and what Factory you're looking for, like so:
=MAX(COLLECT([Date Raised]:[Date Raised], [Accident, Incident or Near Miss]:[Accident, Incident or Near Miss], CONTAINS("Accident", @cell), Site:Site, "Factory 1"))
If this doesn't work, can you post the formula you're currently trying and the error message you're receiving?
Thanks!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
I have managed to create a formula to pull the date of the last Accident, Incident or Near Miss for Factory 1 using this:
=MAX(COLLECT(Date:Date, Site:Site, Site1))
However, I need to be able to drill down the criteria for each type. I have attempted pre-fixing this with an IF statement for "Accident" and it comes back with UNPARSEABLE or INCORRECT ARGUMENT SET.
I have also tried =MAX(COLLECT(AND to combine the date, site and type but no variation has worked for me so far.
Thanks,
M
-
Hi @Mick Tim
The COLLECT function should allow you to enter a new range (column) and criteria to make it have multiple criteria.
Structure example:
=MAX(COLLECT([Column to MAX]:[Column to MAX], [First Column]:[First Column], "First Criteria", [Second Column]:[Second Column], "Second Criteria"))
This means you should be able to add on to your working formula by listing the Site column and what Factory you're looking for, like so:
=MAX(COLLECT([Date Raised]:[Date Raised], [Accident, Incident or Near Miss]:[Accident, Incident or Near Miss], CONTAINS("Accident", @cell), Site:Site, "Factory 1"))
If this doesn't work, can you post the formula you're currently trying and the error message you're receiving?
Thanks!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 429 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!