Excel, Add auto Sequence number

Question

I need to ad the next sequence number in Column A automatically when I fill enter the next value in Column B. This sounds confused. Just see the snap so you will get the clear picture.

This should be done without usual dragging option. Is there any way

berserck · Accepted Answer · 2017-12-12 07:08:50Z

2

Make the Value of A1 equal to 1 and then from the A2 use the formula:

=IF(B3<>"",A2+1," ")

Drag this formula for the whole column.

In my solution I have a drag, but it is to define the formula for each of the fields. (I'm not sure if is this you are trying to avoid when you say

without the usual dragging option

)

answered Dec 12, 2017 at 7:08

berserck

5246 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ceranda Over a year ago

This is what i noted. After read your answer I realize how simple it is. But didn't thought it by myself. Thanks

Aditya Pansare · Accepted Answer · 2017-12-12 07:10:34Z

0

You may achieve this using macros. Formula will increase the file size and processing time.

Right click on the sheet name on sheet tab-->select view code-->paste below code--> save file as macro enabled workbook.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo step
If Target.Column = 2 And Target.Value <> "" Then
Target.Offset(0, -1) = Target.Row - 1
End If

Exit Sub

step:
Exit Sub

End Sub

answered Dec 12, 2017 at 7:10

Aditya Pansare

1,1329 silver badges14 bronze badges

Comments

aaa · Accepted Answer · 2017-12-12 07:17:23Z

0

You can also do this without using macro and formula:

Type first 2 value to the cell to establish the pattern
highlight the cell range
Under Home tag -> select Fill -> choose Series
Follow the image and Select OK

answered Dec 12, 2017 at 7:17

aaa

8584 gold badges26 silver badges46 bronze badges

Comments

Tim Williams · Accepted Answer · 2017-12-12 07:18:30Z

0

If you convert your table to a List ("Table") and use a formula for the first column, that formula will "auto-extend" as new values are typed in under "Item"

answered Dec 12, 2017 at 7:18

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Collectives™ on Stack Overflow

Excel, Add auto Sequence number

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related