Auto Number Formular

Options

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.

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Hargreaves ✭✭
    Answer ✓
    Options

    @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


Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!