Auto Generate a Number based on Column not being blank.



I am trying to get an auto generated number in Auto Number column. I cannot use auto number given that i have conditions to it. I want my Autonumber column to have a formula where it will create a sequential number based on the Hold Issuance date not being blank. I only want it to autonumber if the hold issuance date has a date in it.

=IF(ISDATE([Hold Issuance Date]@row), COUNTIFS([Hold Issuance Date]1:[Hold Issuance Date]@row, "<>"), "")

I used this formula and it works but i cannot make it a column formula due to the 1 being in it. Is there a formula i can use in the sheet summary that i can reference instead?



  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭

    Hello @tchav,

    I wold suggest solving this by:

    • Making new Sheet with an Auto (Number) column in your desired format.
    • Setting up a Copy Row Automation to copy rows in your existing Sheet to this new Sheet whenever Hold Issuance Date is populated with a Date.
    • Create a lookup formula in your existing Sheet that pulls in the Auto (Number) from your new Sheet (you will need some unique identifier to query against).

    Hope this helps!

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!