Looking for a formula that will auto increment number every day based on other cells assigned values
Essentially I am looking to count days and issue is open and assigned to our company.
I was able to somewhat achieve this using a second sheet but there is an administration overhead to this. Issues were copied to second sheet every night and then column in main sheet incremented the days with Team column based on current assigned Team and Status
IE
=IF([Assigned Company]@row = "Our Company", IF([Summary Status]@row = "Open", 1, 0), 0) + +IFERROR(INDEX({OldIssues-DaysPP}, MATCH([Ref #]@row, {OldIssues-REF}, 0)), 0)
Any ideas on how to achieve in a single sheet?
Thanks in advance
Answers
-
Also Tried this in single sheet
IF(AND([Assigned Company]@row = "Our Company", [Summary Status]@row = "Open"), [Days with PP]@row + 1, [Days with PP]@row)
- this returns a circular Reference error
- I think it would just keep incrementing as no data reference
-
Yes, I am calculating the age of something based on a Purchase Date. The first is Today's Date using the formula
The second is Age using the formula
-
The result is
-
Hey Tammy, Unfortunately I already have a days open option and need to only count days if Status is Open and Company Assigned = Our Company
-
Are you able to provide a screenshot for reference (mocked up data is fine / just need to see overall structure and some sample data).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Essentially 3 of the above columns are what needs to be involved.
If Summary status = "Open" AND Assigned Company = "Pinpoint" then increment Days with PP on every day.
Essentially tracking time that an open issue stays with my team vs client.
-
Hi @RHOFF
I hope you're well and safe!
Two options come to mind.
- Workflow combined with the change a cell action and multiple condition paths referencing the current number. (If it's 0, change to 1, if it's 1, change to 2...)
- Workflow combined with the copy row action that would copy the row each time the criteria is true, and then you'd add a formula that counts how many rows that match in the so-called helper sheet.
Would any of those options work/help?
I hope that helps!
Be safe, and have a fantastic weekend!
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.
-
You would need to somehow log the date that you want the counter to start and stop at. If you are not doing this manually, you can set up a Record A Date automation.
Then you would use a formula such as this to calculate the number of days:
=NETWORKDAYS([Start Date]@row, IF([End Date]@row <> "", [End Date]@row, TODAY()))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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!