Automatically insert an incrementing number, but allow manual override

[offbyone]

If possible, I'd like to have Excel insert a number that increments by one in the first column of every new row in a given table like so:

GID   FN       LN
800   John    Smith
801   Jill    Rogers
802   Sam     Spade
803   Sally   Spade
804   Bill    Bayes

but that will also allow for manual override by the user, like so:

800
801
802
802 <--- user changed this to 802, which is the same number as cell above
803 <--- incrementing resumed by incrementing cell above by 1

This is like how QuickBooks will automatically increment check and invoice numbers, though it allows the number to be changed and will increment the next one based on the number manually entered.

I tried formulas like:

=A1+1

and:

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1

but these caused errors when sorting.

I also tried Excel's auto incrementing number feature, but have not been able to make it automatically continue the series when a new table row is added.

Does anyone know of a solution?

 

[Yongle]

Welcome to the forum

It is not possible to do what you want by formula
Here is a VBA solution

right-click on sheet tab \ select View Code \ paste code below into code window \ {ALT}{{F11} to go back to Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
        
    If Not Intersect(Range("A3:A" & Rows.Count), Target) Is Nothing Then
        If IsNumeric(Target) Then Target = Target.Offset(-1) + 1
    End If
           
End Sub

Save the workbook as macro-enabled

 

[offbyone]

Thanks so much for the warm welcome and your fast reply, Yongle.

Thank you also for the VBA code; it appeared to do just the trick at first, but there is an unfortunate quirk: cell values in column A that have been manually set change again if highlighted.

Is there some way for the assignments to be set so that they do not change again if merely highlighted?

 

[Yongle]

Replace :

If IsNumeric(Target) Then Target = Target.Offset(-1) + 1

With :

If Target = "" And IsNumeric(Target.Offset(-1)) Then Target = Target.Offset(-1) + 1

 

[offbyone]

Thanks so much, Yongle - you are a wizard! It now works exactly as desired.


Thanks again!