Reset sequence of number back to 1, based on date change.
I am using a column called Date, formatted as Date. My second column is called Row ID which is my Sequence column, and it is formatted as AutoNumber starting at 1. When the user fills out the Form Date and submits the form, I want the sequence number to increase the RowID By 1, starting at 1 first thing in the morning.
Tomorrow when the date changes, I want the first Row ID to start at 1 again and repeat sequence for that day. I tried mirroring some other Countifs functions, but I am lost..
What would be the best way to accomplish this?
Best Answer
-
Hi, @GRoush_CHC , here's one approach.
Ensure that your auto-number column has no prefix nor suffix (so that it remains a number instead of a string/text), and rename it.
Create a new "Sequence" column and place the column formula:
=COUNTIFS(Date:Date, =Date@row, AutoNum:AutoNum, <=AutoNum@row)
Hope this helped!
Answers
-
Every row in an Auto-Number column is meant to be unique, so it's not going to let you set up a situation where you get multiple rows numbered with the same number the way you want. However, you could make an auto-number column that does include the date as part of a prefix or suffix in the auto number column - see this article:
-
Hi, @GRoush_CHC , here's one approach.
Ensure that your auto-number column has no prefix nor suffix (so that it remains a number instead of a string/text), and rename it.
Create a new "Sequence" column and place the column formula:
=COUNTIFS(Date:Date, =Date@row, AutoNum:AutoNum, <=AutoNum@row)
Hope this helped!
-
Courtney, thanks for the reply, but using the route Toufong mentioned is working perfectly. I was even able to add in other criteria to count on which makes it even better. We are using the following Fx.
=COUNTIFS(Date:Date, =Date@row, ActionNum:ActionNum, <=ActionNum@row, Autoclave:Autoclave, =Autoclave@row )
-
Nice! I'm glad Toufong's solution worked so well for you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!