Referencing another sheet to have automated assigning of files

David Welsh

I have attempted, several times, to follow the exact formula provided on AI so that I can assign new rows to random users.

The AI formula showed: =IFERROR(INDEX({Team Sheet Names}, MATCH(MOD([Row ID]@row , MAX({Team Sheet Order})) + 1, {Team Sheet Order}, 0)))

Following the directions and making the sheet reference, I created: =IFERROR(INDEX({Teams Sheet Range 1}, MATCH(MOD([Row ID]@row , MAX({Teams Sheet Range 2})) + 1, {Teams Sheet Range 2}, 0)))

I was able to set it as a column formula and it did not stop me from doing that but each time I add a new row, I get "incorrect argument: for each row.

I do not know what I am doing wrong.

Any help would be much appreciated.

Thank you!

jmyzk_cloudsmart_jp

Hi @David Welsh

Simple answer is that both AI and you forgot to add the second argument for IFERROR.
Without a default value (like ""), Smartsheet will throw an incorrect argument error.

Also, in your formula, the value being MATCHed (MOD([Row ID]@row , MAX(...)) + 1) always falls within the valid range of your {Team Sheet Order} list, so MATCH will never return an error as long as your lookup range is correct.
That means you don’t actually need IFERROR here at all—it's just hiding a deeper issue.

So the clean version would be:

=INDEX(

  {Teams Sheet Range 1},

  MATCH(

    MOD([Row ID]@row, MAX({Teams Sheet Range 2})) + 1,

    {Teams Sheet Range 2},

    0

  )

)

Or, if you still want to be defensive:

=IFERROR(

  INDEX(

    {Teams Sheet Range 1},

    MATCH(

      MOD([Row ID]@row, MAX({Teams Sheet Range 2})) + 1,

      {Teams Sheet Range 2},

      0

    )

  ),

  ""

)

Example below uses range names like 'Team Sheet Names', not like your 'Teams Sheet Range 1'. It is a good practice to use a range name convention like 'Sheet Name: Column Name' or 'Sheet Name - Column Name' as it is easy to understand the formula.

https://app.smartsheet.com/b/publish?EQBCT=27e01f0bcec74c43b13e351ff7b29e5e

https://app.smartsheet.com/b/publish?EQBCT=161e1ce4734246ca8ca868057d381e64