I am trying to count documents and save a snapshot for a specific month
I am counting documents in one column "SHEQ Document Index Annual Review Range 2" with a specific dropdown "[Document Status]@row" for a specific month and year. However in the following month when the "[Document Status]@row" changes status for that same document, I do not want the Count for the previous month to change, it needs to be static. The below is the formula I used. I tried using the IF Statement, but could not succeed
=(COUNTIFS({SHEQ Document Index Annual Review Range 2}, [Document Status]@row, {SHEQ Document Index Annual Review Range 3}, "2024", {SHEQ Document Index Annual Review Range 5}, "2"))
Answers
-
Hi @Romar
I assume you already have columns capturing the month and year of the status changes. If you add that to your collect statement as an additional parameter, you should be able to get the changes made by month and year. For the closing, if you anticipate no changes at the end of the month for a row, you can look up the month and year from the modified date column (add it in, if you haven't already). I can share a formula if you can add some source data for the reference columns.
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
I have pasted the source data above
-
Hi @Romar
Can you clarify, are you adjusting the same rows in the source sheet with new data, but you want the old formulas to stay static in your metric sheet?
Formulas can only look at the current data that's being displayed on the other sheet - there isn't a way to use a formula to look at the cell history and stay static.
If you're on an enterprise plan, you could potentially use Work Insights on the side panel to see data across time, here's more information: Use Work Insights to visualize your data
Cheers,
Genevieve
-
Can you clarify, are you adjusting the same rows in the source sheet with new data (drop down), but you want the old formulas to stay static in your metric sheet? "Yes", however for the previous month and not the same month which the row is been changed for.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!