Rolling number

Johno251

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!!

Paul Newcome

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

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, "")

David Jasven

You can try use a combination of IF, MOD, and ROW functions to create a cycle of numbers from 1 to 12. However, Smartsheet doesn't have a built-in RUNNING_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.

Johno251

@David Jasven

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?

Paul Newcome

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, "")

Johno251

@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 ?

Paul Newcome

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, "")