Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Increment numbers in a column
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)? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 Liz wrote: 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)? -- Dave Peterson |
#4
|
|||
|
|||
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" wrote: 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 |
#5
|
|||
|
|||
It appears Dave Peterson has your answer.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
column filtering to match identical numbers | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Average of numbers in column between to other numbers | Excel Discussion (Misc queries) | |||
add only positive numbers and avg in a column | Excel Worksheet Functions | |||
How do I increment or decrement values in a column? | New Users to Excel |