Auto Number Formular

Hargreaves

We find the Smartsheet numbering system not very good as if a value is inputted accidentally and removed it wrecks the auto number and find you have to change from auto number to text/number and repair the auto number and reconfigure it.

So I have been asked to create an auto number formular but with a catch. They one want the auto number to be added to a row. Once a failed check box is selected and this could located anywhere in the sheet ie row 10 then row 20 or row 50 etc.

I suspect this wouldn't be possible as Smartsheet cannot workout the last failed tick box and what auto number was assigned to that row for example QRN-001 in row 32 and the next fail could be in row 56 and would need to be QRN-002.

They have now decided to use a manual input rather than looking at a formular to achieve the results they are after.

I was wondering if anyone had come across this at all.

Paul Newcome

In that case you can insert an auto-number column (called "Auto" in this example) with no special formatting and then use this as a column formula:

=IF(Fail@row = 1, "QRN-" + IF(COUNTIFS(Fail:Fail, @cell = 1, Auto:Auto, @cell<= Auto@row)< 10, "00", IF(COUNTIFS(Fail:Fail, @cell = 1, Auto:Auto, @cell<=Auto@row)<100, "0", "")) + COUNTIFS(Fail:Fail, @cell = 1, Auto:Auto, @cell<= Auto@row))

Hargreaves

@Paul Newcome, thanks for your assistance using your formular i have come up with my own solution after a light bulb moment that has 4 helper columns which are:

QRN which has a formular of: ="QRN"

QRNNUM which has a formular of: =IF(Fail@row = 1, IF(COUNTIFS(Fail:Fail, @cell = 1, RowID:RowID, @cell <= RowID@row) < 10, "00", IF(COUNTIFS(Fail:Fail, @cell = 1, RowID:RowID, @cell <= RowID@row) < 100, "0", "")) + COUNTIFS(Fail:Fail, @cell = 1, RowID:RowID, @cell <= RowID@row))

AutoCount which has a formular of: =1 + 0

RowID which has a formular of: =COUNT(AutoCount$1:AutoCount1)

QRN Number which has a formular of: =IF(Fail@row = 1, JOIN(QRN@row:QRNNum@row, "-"))

This works perfectly and I can delete rows and the sequence runs correctly.

The only issue is QRN Number, QRN, QRNNUM, AutoCount are all column formulas but the RowID cannot as it doesn't match the syntax on I have worked this out if I can it should work perfectly otherwise it be a manual drag down to populate the cells

Paul Newcome

Are you able to provide some screenshots for context?

Hargreaves

@Paul Newcome

This is only me test data but I don't think its possible as i cannot find a formular that can keep the numbering sequential.

Paul Newcome

It can be possible through formula depending on specifics. I use formulas very frequently for establishing sequential numbering.

Would they be marked as pass/fail sequentially, or could you mark pass/fail on row 53 and then later on mark pass/fail on row 21? Are you able to provide more details as to the actual workflow of the data?

Hargreaves

@Paul Newcome This is the problem, once a failed item is checked it needs a sequential number now this can appear in any row we could have 100 pass or just 50 pass then we could have another 200 pass and then a failure and need to carrying on from the last failure QRN number if you need more info please ask

Paul Newcome

I understand all of that. What I need to know is how the sheet is filled out. What order do things get done in?

Hargreaves

@Paul Newcome The sheet is all filled out manually but when a failure is ticked in should automatically give the QRN number the next sequential number starting with QRN-

Paul Newcome

I understand that, but it still doesn't answer my question.

When you are filling in the sheet, do you start at the top and work your way down, or do you skip around? Forget about the sequential numbering for a minute. I need to know how the sheet gets filled out. How are new rows added? When they are added, is the pass/fail already marked, or is that something that gets done later? If done later, do you work your way down from the top marking the pass/fail, or do you skip around?

Hargreaves

We don't skip around, it be filled out top to bottom and every row will be a product line and the pass and fail will be filled out on every row when a product is inputted

Paul Newcome

In that case you can insert an auto-number column (called "Auto" in this example) with no special formatting and then use this as a column formula:

=IF(Fail@row = 1, "QRN-" + IF(COUNTIFS(Fail:Fail, @cell = 1, Auto:Auto, @cell<= Auto@row)< 10, "00", IF(COUNTIFS(Fail:Fail, @cell = 1, Auto:Auto, @cell<=Auto@row)<100, "0", "")) + COUNTIFS(Fail:Fail, @cell = 1, Auto:Auto, @cell<= Auto@row))

Hargreaves

@Paul Newcome Thanks for this it does do the job, but is there anyway we can not use a auto number as if a row is deleted it throws the auto numbering out of sequence and you have to edit the auto number. If not I will advise this is the only option we have.

Hargreaves

@Paul Newcome Doing some testing it actually works correctly even deleting a row thanks for this.

Hargreaves

@Paul Newcome Doing further testing it does not quite fit the bill once it gets to 10 it goes out of sequence and found the answer its the auto number causing the number once the correct auto number is back in sequence the formular works as expected.

Hargreaves

@Paul Newcome, thanks for your assistance using your formular i have come up with my own solution after a light bulb moment that has 4 helper columns which are:

QRN which has a formular of: ="QRN"

QRNNUM which has a formular of: =IF(Fail@row = 1, IF(COUNTIFS(Fail:Fail, @cell = 1, RowID:RowID, @cell <= RowID@row) < 10, "00", IF(COUNTIFS(Fail:Fail, @cell = 1, RowID:RowID, @cell <= RowID@row) < 100, "0", "")) + COUNTIFS(Fail:Fail, @cell = 1, RowID:RowID, @cell <= RowID@row))

AutoCount which has a formular of: =1 + 0

RowID which has a formular of: =COUNT(AutoCount$1:AutoCount1)

QRN Number which has a formular of: =IF(Fail@row = 1, JOIN(QRN@row:QRNNum@row, "-"))

This works perfectly and I can delete rows and the sequence runs correctly.

The only issue is QRN Number, QRN, QRNNUM, AutoCount are all column formulas but the RowID cannot as it doesn't match the syntax on I have worked this out if I can it should work perfectly otherwise it be a manual drag down to populate the cells