Auto number with specific values



I'm a beginner in Smartsheet and I want to move every 5th row entry into another sheet

For this, I've tried adding a "help" column to number all of the entries, but I need this to be generated automatically every time someone enters data on a row

Is there a way to set the auto-number column property with limited values?

Best Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 07/13/21 Answer ✓

    One approach is to use an auto-number column along with: a helper column, the MOD() function, workflow automation.

    If auto-number starts with "1", then every 5th row that is created will contain a number that is divisible by "5". A helper column can "monitor" this. The helper column can be the trigger that causes the row to be moved.

    Below is a mockup of how this would be set up.

    Here's the formula for the helper column.

    =IF(MOD([Auto Number Column]@row,5)=0, 1, "")

    The auto-number will allow you to automate the value in QA Tracker as well.

    QA Tracker = IF(MOD([Auto Number Column]@row,5)=0, 5, MOD([Auto Number Column]@row,5))

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    edited 07/13/21 Answer ✓

    IF your Part Number is already your auto-number column, then your function for the helper column would be,

    =IF( MOD( VALUE( SUBSTITUTE([Part Number]@row, "CFVDH",""))-6,5)= 0, 1, "")

    The formula uses

    1. SUBSTITUTE() to replace "CFVDH" with a blank;
    2. VALUE() converts the remaining characters into a number;
    3. Subtract 6 from that;
    4. MOD() checks to see if the resulting number is divisible by 5.

    IF() it is divisible by 5, then places a 1 into the cell; otherwise, leave it blank.