# Autonumbering

I have two tables in one excel workbook under each other. Each table contains the first column which is just a number, 1,2,3....

Problem: when a record is deleted, you need to renumber the rows.

Would like: is it possible that when you add or remove a row it automatically renumbers the rows?

I have done this by inserting a table in excel and using this formula =ROW()-3. However it only works with one table. the second table updates numbers wrongly because the row number changes.

Thank you.
###### Who is Participating?

Commented:
OK attached is something I think will work as required:

=COUNTA(B\$2:B3)+COUNTBLANK(B\$2:B3)-1

See attached.
CountA.xlsx
0

Fixer of ProblemsCommented:
I would put '1' in the top row and put "=A1+1" in the second and copy it all the way down so it just recalculates all the time.  The only 'absolute' number is at the top of the column.
0

Commented:
As DaveBaldwin says ^
0

Author Commented:
When you insert a new row there is no number there, it doesn't automatically insert the right number.
0

Finance AnalystCommented:
Using simple formula of for example "=A3+1" will cause problems when row 3 is deleted, you will end up with a #REF error because it will no longer recognise row 3.

I do similar using INDIRECT:

=INDIRECT("A"&ROW()-1)+1

This will always return the row above +1 when rows deleted or sorted into a different order.

It doesn't get round when rows are inserted unless you copy down from row above when you insert a row.

Thanks
Rob H
0

Commented:
try using:
=SUBTOTAL(3,A\$3:A3)
Where A3 is the header row.
0

Author Commented:
Rob,
That's a good suggestion.

However, Is there a better solution where autonumbering occurs even when rows are inserted?
0

Author Commented:
The_Barman,

I don't understand how to use the formula you provided.
0

Commented:
Attached is a workbook as example

I have tested it and COUNTA works on its self where SUBTOTAL(3,) doesn't on its self.

So you could use =COUNTA(A\$2:A2)
but the formula doesn't update on insert new rows like subtotal does.

See attached.
CountA.xlsx
0

Author Commented:
The_Barman,

That works a treat. Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.