Increment numbers in a column

[Guest]

I know that if I drag a cell with a number in then the cells below will be
filled in with incremental numbers, however, is there a way to set a column
so that it auto-populates with incremental numbers each time I type in a new
row (the same as would happen in an MS Access table where the first column
was set to auto-populate with a unique number)?

 

[DNA]

I think I follow what you're referring to...

Go to the cell you want to increment and merely type @SUM(A1+1) in A2
where A1 contains a value and in this example, if A1 was 1, A2 would
automatically become 2 ... copy the formula down the various rows.

HTH

 

[Dave Peterson]

You could use an event macro that adds that number.

Right click on the worksheet tab that should have this behavior. Select view
code and paste this into that code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("b:b")) Is Nothing Then Exit Sub
If Target.Row < 3 Then Exit Sub

On Error GoTo errHandler:

With Target
If IsEmpty(.Offset(0, -1)) Then
If IsNumeric(.Offset(-1, -1).Value) Then
Application.EnableEvents = False
.Offset(0, -1).Value = .Offset(-1, -1).Value + 1
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

It assumes that you're entering data in nice order (no skipped rows). Typing in
cell B3 will take the value in A2 and add one to it.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about these kinds of events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

 

[Guest]

Thanks DNA!

Any ideas as to whether I can get the incremental numbers to only appear
when I type in the row....so for example if I've got 10 rows of data (which
have the incremental number in the first column), then I don't want number 11
to show until I put something in that row...

Cheers!

 

[DNA]

It appears Dave Peterson has your answer.