Creating a Counter
Does anyone know of a way to create a counter so everytime a cell becomes a specific work it adds one to the counter. As a coder I would use a loop but I am not sure how to approach this from a formula perspective or if its even possible.
Answers
-
What kind of scaling do you need?
-
Just up by 1 each time.
Example how many times has this cell become status late for a project
-
Right. Sorry. I meant how often would the cell change to whatever you needed to track and how many rows are in the base sheet?
The basic idea is that you have some kind of unique indicator on every row (or a combination of columns that creates a unique indicator) and then set up a copy row automation to run when that column changes. You would then use a COUNTIFS with cross sheet references to count how many times that indictor (or series of columns generating an indicator) is found on the second sheet.
This works as long as we stay within certain limitations such as sheet size and referenced cells and whatnot, but if you have thousands of rows changing a lot that you would nee recorded, it may not be the solution you are looking for (may need to leverage the API).
-
currently the base sheet goes up to 90 rows and growing with upwards of 70 columns it is my large research sheet where we correlate data and then direct from there so adding more columns solely for the purpose of counting is worrying. I know you cant use circular logic in these but i was hoping someone would have a sneaky trick lol
-
Oh yes and they potentially change on the daily as the dates in them adjust
-
Hi,
I hope you're well and safe!
How high do you need to counter to be able to go?
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Ok. So far we aren't in too bad of shape. I understand things can change daily, but how many times in total would be an expected number of changes for a row? I imagine eventually things will start getting closed out?
-
Hopefully the counter need go no higher than 5 to ten , but it will be per project per department so potentially max 50
-
What do you mean by per project per department? Can you elaborate?
My idea was to use a Workflow with the Change Cell Action, which only makes sense for ten changes per row.
It can handle more, but then it might be better to develop a solution that uses the method Paul mentioned with a helper sheet.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Are you able to provide a screenshot of the main sheet (sample data is fine) for context?
-
@Andrée Starå I'm curious how you would use a Change Cell automation for a counter?
-
Advisor has requested i build a temporary sheet with an example. You will see the behind and on time is run using if statements where it check the finish date against todays date and checks for a completion mark. The goal is if department 1 gets behind its tracked even if we have to adjust the date and is behind again now we know they were behind twice. then in our separate data form i would be summing up per department so we know if we need more training or researchers.
Thanks in advance
-
i did look into the automation of changing cell values but it would only let me change it to a fixed value
-
Ok. So are the date columns supposed to be "expected" finish then? What is the formula you have in the column populating "Behind"?
-
=IF(AND([Department 1 finish Date]@row < TODAY(), [D1 finished]@row = false), "BEHIND", IF(AND([Department 2 finish date]@row < TODAY(), [D2 finished]@row = false), "BEHIND", IF(AND([Department 3 finish date]@row < TODAY(), [D3 finished]@row = false), "BEHIND", IF(AND([Department 4 finish date]@row < TODAY(), [D4 finished]@row = false), "BEHIND", IF(AND([Department 5 finish date]@row < TODAY(), [D5 finished]@row = false), "BEHIND", "ON")))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!