Inserting Serial Number

[FarzanaSaleem]

In an Excel Sheet, first column is Serial Number. This excel file is shared and used by all team members to enter goals for the coming month.

I used =ROW()-1 in cell A2 and pasted this formula up to A500. Now when I deleted Serial No. 5 (Row 6), then all serial numbers adjusted accordingly.

I need one more feature in this sheet.

When a person inserts a new row between two serial numbers, then serial numbers below it adjust accordingly but the inserted row shows a blank. For example, if I insert a new row between Serial No. 5 and 6 (A6 and A7), then

A6 = 5
A7 = BLANK
A8 = 7

I need A7 to show 6 automatically.

 

[combo]

Do you really need a number here? Your data can move up and down and you accept changing serial numbers. Excel has numbered rows, and what you need is the number less 1.

Anyway, you can use VBA, rightclick sheet's tab, choose 'view code' and paste the code below:
[tt]Private c As Range

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Columns("B:IV&quot) Is Nothing Then
For Each c In Target.Columns(1).Cells
If c.HasFormula = False Then
c.EntireRow.Cells(1, 1).FormulaR1C1 = "=ROW(RC)-1"
End If
Next
End If
End Sub[/tt]

Cells in column A should be cleared without without selecting other data.

combo