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

Answers

  • David Jasven
    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.

  • @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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    Johno251 ✭✭
    edited 02/07/24

    @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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!