Dynamic Arrays & Lookup
Automatic Row Numbers That Expand Dynamically and Skip Blank Separator Rows
Number task lists automatically without broken #REF! chains using SEQUENCE(COUNTA(...)) for contiguous blocks or IF(ISBLANK(...), "", COUNTA($B$2:B2)) for tables with blank separator rows.
Contiguous SEQUENCE spills vs. gap-tolerant expanding COUNTA
Manual =A2 + 1 row numbers break into #REF! the moment someone deletes a middle row. For contiguous task lists with no blank gaps, a single formula =SEQUENCE(COUNTA(B2:B10)) in A2 spills 1..N automatically as items are added or removed.
When an action tracker includes intentionally blank spacer rows between sections, SEQUENCE would number the blank rows too. Using =IF(ISBLANK(B2), "", COUNTA($B$2:B2)) leaves spacer rows blank and continues sequential numbering seamlessly on the next populated row—including when teammates or the Arte Sheets AI Assistant insert or delete 1-based rows (up to 20 at a time).
| Row | Task Description (Col B) | Gap-Aware Formula: IF(ISBLANK(B2), "", COUNTA($B$2:B2)) |
|---|---|---|
| 2 | Finalize schema | 1 |
| 3 | Seed test fixtures | 2 |
| 4 | (Blank section break) | "" |
| 5 | Run QA suite | 3 |
| 6 | Publish release notes | 4 |
Gap-Tolerant Sequential Row Numbering
Displays "" on blank rows and increments by 1 only when column B holds a populated item.
=IF(ISBLANK(B2), "", COUNTA($B$2:B2))Single-Cell Spilled Row Numbers for Contiguous Lists
Spills 1..N down from the top row based on the count of populated items in B2:B6.
=SEQUENCE(COUNTA(B2:B6))Try the example
Use the original values or change them to test your understanding.