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.
anya_morrisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer 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
JBrITCommented:
As DaveBaldwin says ^
0
anya_morrisAuthor Commented:
When you insert a new row there is no number there, it doesn't automatically insert the right number.
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Rob HensonFinance 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
SteveCommented:
try using:
=SUBTOTAL(3,A$3:A3)
Where A3 is the header row.
0
anya_morrisAuthor Commented:
Rob,
That's a good suggestion.

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

I don't understand how to use the formula you provided.
0
SteveCommented:
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
SteveCommented:
OK attached is something I think will work as required:

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

See attached.
CountA.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anya_morrisAuthor Commented:
The_Barman,

That works a treat. Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.