Formula to Create Unique ID on Specific Rows

Hannah H

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!

David Hersher

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

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