# Automatic increment in a number

#### Jackewing98

##### New Member
Hi all

I am looking for some further help if possible?

I have a database, in column one I have numbers i.e.

A1 - 1
A2 - 2
A3 - 3

This database holds the details of employees for the company by alphabetical order. When a new member of staff joins there details are inserted into the relevant section. How do I get the numbers to automatically work out the new number?

For example

A1 - 1 B1 - Adams
A2 - 2 B2 - Beach
A3 - 3 B3 - Clarke

If then I have someone start with surname of Blake I have to insert a row between A2 and A3 but the new entry to read employee 3.

A1 - 1 B1 - Adams
A2 - 2 B2 - Beach
A3 - 3 B3 - Blake
A4 - 4 B4 - Clarke

Thanks

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### RalphA

##### Well-known Member
Without resorting to VBA, I would use something like this:
A1 =ROW()
and copy down. You will see:
A2 2 B2 Beach
A3 3 B3 Clarke

Now, you inser a new row A3, and get:
A2 2 B2 Beach
A3
A4 4 B4 Clarke

Copy A2 to A3, getting:
A3 3

Now, enter the rest of the data for new row 3.
B3 Blake

Let us know if the above solves your problem

Oops!

#### Datsmart

##### Well-known Member
Put this formula in cell A2 and copy down.
Code:
``=A1+1``
Then when you insert a new row, copy the formula down again through the new row.

#### Datsmart

##### Well-known Member
I like RalphA's idea of using ROW().
But if you add a header row and move your data down, the numbers will pickup their new row numbers, this may not be desirable. But it works fine in your example.

#### RalphA

##### Well-known Member
In case your first row with data (your 1) is not row 1, just compensate. Thus, if your first data row is row 3, then, in A3, enter =ROW()-2. All the rest remains the same.

Replies
1
Views
212
Replies
6
Views
222
Replies
2
Views
176
Replies
3
Views
482
Replies
10
Views
462

1,190,629
Messages
5,982,020
Members
439,750
Latest member
megaman777

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back