Auto number with specific values

Aldo Perez

Hi

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?

Toufong Vang

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

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.

Andrée Starå

Hi @Aldo Perez

I hope you're well and safe!

Interesting!

How many rows can there be in total in the sheet?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Bassam Khalil

Hi @Aldo Perez

Hope you are fine, please check the following post it will help you Work Breakdown Structure (WBS) - Column Formula Configuration

Toufong Vang

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

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.

Aldo Perez

Hi @Andrée Starå

There can be thousands of rows in the sheet

Aldo Perez

Thank you both @Bassam Khalil and @Toufong Vang

@Toufong Vang that formula does the trick in this case, thanks!

Aldo Perez