Formula to Create Unique ID on Specific Rows

Options
Hannah H
Hannah H ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hi Everyone,

I am trying to create a unique ID but only on specific rows, so the auto-number column type is not suitable. I would like the ID to be sequential. 

I started by generating a number, 1, where Course and Action Due Date are blank. I have successfully done this by using the formula:

=IF(AND(ISBLANK(Course491), ISBLANK([Action Due Date]491)), 1, "")

Not really sure if this is the right approach or where to go next? Perhaps something like If the cell above ISNUMBER, +1 ???

Any ideas appreciated. 

Thanks!

Tags:

Comments

  • David Hersher
    edited 02/07/19
    Options

    Hi Hannah,

    I think maybe this could work.  I got it to work over 11 rows or so.  For me, the column I was trying to number was COLUMN3.  Adjust for your needs:  

    First Row where you want the numbering to start at "1" if COURSE and ACTION DUE DATE are blank (adjust the row reference for your needs):

    =IF(AND(ISBLANK(Course1), ISBLANK([Action Due Date]1)), 1, " ")

    The next row, if it's consecutive:

    =IF(AND(ISBLANK(Course2), ISBLANK([Action Due Date]2)), [Column3]1 + 1, " ")

    Each subsequent row:

    =IF(AND(ISBLANK(Course3), ISBLANK([Action Due Date]3)), MAX([Column3]$1:[Column3]2) + 1, " ")

    As you copy down the column, it should look for the max value from the first row through the row right above it and add 1 to that value if the COURSE and ACTION DUE DATE columns are both blank.  Otherwise, the cell will be blank.

    Did that make any sense? 

    Dave

  • Hannah H
    Hannah H ✭✭✭✭✭
    Options

    Hi Dave,

    Yes, this works exactly as promised - thank you! I haven't had the need to use the MAX function before and it works great in this intance.

    Hannah

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!