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

Options
✭✭✭

Hi,

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!

• ✭✭✭✭✭✭
Options

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

• Overachievers Alumni
Options

@Paul Newcome do you know how to solve it?

• ✭✭✭✭✭✭
Options

Are you able to provide some screenshots for context? I believe I understand the ask and can help with a formula but want to make sure I don't get things too mixed up.

• ✭✭✭
Options

Hi Paul,

appreciate your quick feedback. Here is a screenshot of my google spreadsheet (how it works) and a screenshot of the modified smartsheet formula.

• ✭✭✭✭✭✭
Options

So basically you are trying to take the Reference List column and just repeat it over and over again going down another column?

• ✭✭✭
Options

Yes sir!

• ✭✭✭
edited 12/25/23
Options

i solved it with the index and modulo function in google sheets. Somehow i cannot figure out why it doesn't work in Smartsheet although the same functions are also available there. I think its just a syntex issue but i cannot solve it.... Your help is greatly appreciated!

• ✭✭✭✭✭✭
Options

You would need to insert an auto-number column with no special formatting and then use this:

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

• Overachievers Alumni
Options

Hello @Paul Newcome,

Thanks for your help when creating this formula and it works almost perfectly but.... look at my screenshots:

I tried to fix this issue with iferror function to put "e" when error but still shows the invalid column do you know how to fix it?

@nisanb this is the function for you to assign your POC automatically one by one :)

• ✭✭✭✭✭✭
Options

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

• Overachievers Alumni
Options

It works! @Paul Newcome you rock

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!