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

Dr. Kadir

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!

Paul Newcome

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

kowal

@Paul Newcome do you know how to solve it?

Paul Newcome

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.

Dr. Kadir

Screenshot 2023-12-21 at 2.30.20 PM.png

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.

Screenshot 2023-12-21 at 2.34.55 PM.png

Appreciate your support mate!

Paul Newcome

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

Dr. Kadir

Yes sir!

Dr. Kadir

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!

Paul Newcome

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

kowal

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

Paul Newcome

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

kowal

It works! @Paul Newcome you rock

Paul Newcome

Happy to help. 👍️