=INDEX($A$1:$A$5, MOD(ROW() - 1, COUNTA($A$1:$A$5)) + 1)
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!
Best 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 <> ""))))
Answers
-
@Paul Newcome do you know how to solve it?
The Real Smartsheet Enthusiast
Is there anything else we can help you with? - book your time!
MASA Consult - Your Aligned Smartsheet Partner
Find us on LinkedIn!
-
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.
-
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.
Appreciate your support mate!
-
So basically you are trying to take the Reference List column and just repeat it over and over again going down another column?
-
Yes sir!
-
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!
-
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 <> "")))
-
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 :)
The Real Smartsheet Enthusiast
Is there anything else we can help you with? - book your time!
MASA Consult - Your Aligned Smartsheet Partner
Find us on LinkedIn!
-
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 <> ""))))
-
It works! @Paul Newcome you rock
The Real Smartsheet Enthusiast
Is there anything else we can help you with? - book your time!
MASA Consult - Your Aligned Smartsheet Partner
Find us on LinkedIn!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!