Adding subsequent row ID numbers to prefix if cell is not blank

Linders

Hi all, I'm struggling to create a row ID field where it will populate an ID number ID_RC_1 and so on if the Employee Name cell is not blank. If it's blank then it will populate "-". Is there a way to create sequential numbering to be included after the "ID_RC" prefix?

Looking for : ID_RC_1, ID_RC_2... is name cell is not blank.

Avoiding the auto number system as it populate IDs for rows that are irrelevant.

Thanks in advance for the help!

sharkasits

The only way I can think of to do this is to add 2 helper columns.

1. AN = Autonumber
2. RowNum = IF(NOT(ISBLANK([Employee Name]@row)), INDEX(COLLECT([RowNum]:[RowNum],[AN]:[AN], < [AN]@row, [RowNum]:[RowNum], NOT(ISBLANK(@cell))), COUNT(COLLECT([RowNum]:[RowNum],[AN]:[AN], < [AN]@row, [RowNum]:[RowNum], NOT(ISBLANK(@cell)))))+1,"")
3. Row ID = IF(NOT(ISBLANK([Employee Name]@row)), "ID_RC"+[RowNum]@row,"")

sharkasits

The only way I can think of to do this is to add 2 helper columns.

1. AN = Autonumber
2. RowNum = IF(NOT(ISBLANK([Employee Name]@row)), INDEX(COLLECT([RowNum]:[RowNum],[AN]:[AN], < [AN]@row, [RowNum]:[RowNum], NOT(ISBLANK(@cell))), COUNT(COLLECT([RowNum]:[RowNum],[AN]:[AN], < [AN]@row, [RowNum]:[RowNum], NOT(ISBLANK(@cell)))))+1,"")
3. Row ID = IF(NOT(ISBLANK([Employee Name]@row)), "ID_RC"+[RowNum]@row,"")

Linders

Thank you, I simplified the formula and eliminated the #2 from your answer above for sake of simplicity. The numbering isn't perfect, but it will at least not populate in rows that aren't blank. Appreciate the response!