Excel Table Number Incrementer

[legalhustler]

I have a Excel table. A1 contains the header. From A2:A10 I have the following formula Row()-1 to give me the sequential number increment (1, 2, 3, 4 etc). However if I add row above A1 the numbers mess up (2, 3, 4, 5). I need a dynamic formula that takes into consideration if the rows above contain text or is blank then the number increment should display correctly. Can someone help.

Thanks!

 

[crazydragon84]

i'm not fully understanding. if you add a row at the top, you still want the numbers to say 1, 2,3, 4, 5... i don't get the point about "if the rows above contain text or is blank then the number increment should display correctly". 1, 2, 3, "ABC", 4, 5? you can do =Max(indirect("A$1:A"&Row()-1))+1 maybe?

 

[StephenCrump]

Perhaps: =ROW([@YourColHeader])-ROW(Table1[[#Headers],[YourColHeader]])

 

[legalhustler]

i'm not fully understanding. if you add a row at the top, you still want the numbers to say 1, 2,3, 4, 5... i don't get the point about "if the rows above contain text or is blank then the number increment should display correctly". 1, 2, 3, "ABC", 4, 5? you can do =Max(indirect("A$1:A"&Row()-1))+1 maybe?

Your formula works. If I add rows above my table then the number increment (1, 2, 3, etc) does not change. Is there a way to avoid using volatile functions like INDIRECT?

 

[StephenCrump]

A2: =ROWS(A$2:A2) copy down

 

[crazydragon84]

A2: =ROWS(A$2:A2) copy down

that doesn't work because when you insert a row the numbers change. i'm assuming what he wants to do is have like subtotal or category header in between the numbers.

Legalhustler, maybe as easy as hardcoding A1 (or where ever the first data is) as 1, then in A2 =A1+1 and drag it down? this way you insert a row and the formula will still reference the old cell.