=INDEX($A$1:$A$5, MOD(ROW() - 1, COUNTA($A$1:$A$5)) + 1)


i am trying to write a formula that in a new row it should take the next value from a reference list and when last value in column is taken then it should go back to the first value (like a loop):

=INDEX([Reference Column]1:[Reference Column]5, MOD(@row - 1, COUNTA([Reference Column]1:[Reference Column]5)) + 1)

it works in excel but in Smartsheet it tell me #UNPARSEABLE. Any help is appreciated!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The issue is one I always forget about with the MOD function.

    45, 50, 55, etc. MOD 5 will output zero. Outputting zero for the row number in the INDEX function breaks it. WHat we need to do is the same thing I always end up doing after forgetting about this piece of the MOD function is to use an IF statement that says IF the MOD is equal to zero, output the COUNTIFS, otherwise output the MOD.

    IF(MOD(..........) = 0, COUNTIFS(..........), MOD(..........))

    =INDEX([Reference List]:[Reference List], IF(MOD([Auto-Number Column]@row, COUNTIFS([Reference List]:[Reference List], @cell <> "")) = 0, COUNTIFS([Reference List]:[Reference List], @cell <> ""), MOD([Auto-Number Column]@row, COUNTIFS([Reference List]:[Reference List], @cell <> ""))))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!