Rolling number
Hi all,
I am trying make a helper column (in my calendar I have made) that will give me a number so I can assign that number to a person and they will be on duty when their number comes up. I am using parent rows so I have a level column and I only want this number to appear on the level 1 rows. I need the numbers to go up in order but finish at 12 and start again at 1. I will then use these numbers to assign to contacts off another sheet.
I would like the Duty Student Number Calc to be the numbers 1 - 12. I was trying to use the Running total formula to assist.
I hope this makes sense.... Thanks in advance!!
Best Answers
-
This should work as a column formula:
=IF(Level@row = 1, MOD(COUNTIFS(Level:Level, @cell = 1, [Running Total Formula]:[Running Total Formula], @cell<= [Running Total Formula]@row) - 1, 12) + 1, "")
-
Try this:
=IF(AND(Level@row = 1, [Running Total Formula]@row >= 17), MOD(COUNTIFS(Level:Level, @cell = 1, [Running Total Formula]:[Running Total Formula], AND(@cell<= [Running Total Formula]@row, @cell >= 17)) - 1, 12) + 1, "")
Answers
-
You can try use a combination of
IF
,MOD
, andROW
functions to create a cycle of numbers from 1 to 12. However, Smartsheet doesn't have a built-inRUNNING_TOTAL
function like Excel, so we'll have to simulate it.Let's assume that you want to start your duty numbers at Row 2, and you want the count to only increase on Level 1 rows. Here's a formula you might use in the "Duty Student Number Calc" column in Smartsheet:
=IF([Level]@row = 1, MOD(COUNTIF([Level]$1:[Level]@row, 1) - 1, 12) + 1, "")
This formula does the following:
- Checks if the "Level" of the current row is 1.
- If it is, it counts how many Level 1 rows there are up to the current row.
- It then uses the
MOD
function to cycle through numbers 1 to 12, adding 1 after taking the modulus to ensure the cycle starts at 1 instead of 0. - If the "Level" of the row is not 1, it leaves the cell blank.
Please note that the
$1
in[Level]$1
indicates the starting row for the count. You should replace this with the actual starting row number of your data. This formula assumes that the Level column is named "Level" and that your sheet starts from row 1. If your sheet starts from a different row, you would need to adjust the formula accordingly. -
Thank you for this. It has worked except it wont let me make it a column formula? I can manually add the formula into any row and it works but as soon as I try to go column formula I get this error. Any ideas why this would be happening?
-
This should work as a column formula:
=IF(Level@row = 1, MOD(COUNTIFS(Level:Level, @cell = 1, [Running Total Formula]:[Running Total Formula], @cell<= [Running Total Formula]@row) - 1, 12) + 1, "")
-
@Paul Newcome Thanks for that!! It worked great. Can you tell me where I would add into that formula if I want it to start from row 17?@David Jasven ?
-
Try this:
=IF(AND(Level@row = 1, [Running Total Formula]@row >= 17), MOD(COUNTIFS(Level:Level, @cell = 1, [Running Total Formula]:[Running Total Formula], AND(@cell<= [Running Total Formula]@row, @cell >= 17)) - 1, 12) + 1, "")
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!