AUTOMATIC ROW NUMBERING 2

[xsubzeroz]

I have a table with Column A setup to show topic number. I have about 25 topics listed in table but when I insert row, numbers stay same. I have to go and renumber all rows after I insert row. Is there a way to do this so that when I insert row, it will automatically update row numbering and also number the row that I've inserted. Thanks for help in advance.

 

[AnotherHiggins]

Put this in Column A of the top row of the table:

[blue]=row()[/blue]

Then autofill down.

 

[xsubzeroz]

Thanks a lot "anotherhiggins" this is great, I didn't know this feature. The only problem is that I didn't describe properly. I have Item List starting from ROW 9 and some items have different issues involved that I put in COLUMN B. So I'll have in COLUMN A & ROW 9 "Item #1" but in COLUMN B I'll have under ROW 9 "Issue a", ROW 10 "Issue b" and ROW 11 "Issue c" so I will merge COLUMN A Item #1 in ROW 9,10&11. My next Item will be in ROW 12 therefore =row() will not display the proper sequence. Is there anything can be done. Thanks.

 

[bluedragon2]

yes, in A9, put 1, in the next instance, put:

=COUNTA($A$9:A26)+1

then for the next instance, copy and paste that formula.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AnotherHiggins]

It was probably just an oversight, but if you enter that formula into any cell from A10:A26, you'll get a circular reference.

xsubzeroz,

I think what Blue Dragon meant to say was enter this function in A10:

=COUNTA($A$9:A9)+1

The $'s in the first cell reference ($A$9) will lock the beginning of the range to cell A9, while the lack of $'s in the second half (A9) will allow the range to expand as you autofill down. If you insert 3 rows, then merge column A, this formula will continue to work!

Hope that helps.

John

 

[xlbo]

If you can cope with having spaces in the rows which aren't numbered, then you could use:

=IF(B1=""," ","Item " & COUNTIF(INDIRECT("A1:A" & ROW()-1),"<> ")+1)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[xlbo]

NB - mine can start in row 1 and be copied down to all cells

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question