automatic filling series in cell when a different cell is edited

Question

I want to add a sequential number in column 1 (so A1, B1, c1, would be 1,2,3) WHEN I enter any data in Column 2. So if a cell doesn't have a value, nothing is entered in the other cell.

The end result should look like this.

A- 1    32
B- 2    18
C- 3    16

E- 5     20

It's for a box count and config.

I've tried a few things and I am missing a value. Can any one help please?

Answer 1

Maybe this can help, just in the A1 cell use this formula:

Excel 2007

=IF(LEN(B1)>0;ROW(A1);"")

Excel 2010

=IF(LEN(B1)>0,ROW(A1),"")

And then fill down the cells with the formula. This will write the cell row when it finds anything and a zero when it doesnt:

1   test
2   test
3   test
0   
5   test
0   
7   test

Answer 2

One way to tackle this problem would be to leverage the Worksheet_Change event. This way you do not need to have any formulas in your spreadsheet:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Application.Intersect(Target, Range("B:B")) Is Nothing Then
        End
    End If

    Target.Offset(0, -1) = WorksheetFunction.Max(Range("A:A")) + 1

End Sub

---

To implement this code:

1. In your workbook press ALT + F11 (this opens VBA editor)
2. Open the Project Explorer (CTRL + R)
3. Double-clik the sheet that you want the code to apply to e.g. Sheet1
4. In the left-hand dropdown menu select Worksheet and in the right hand menu select Change
5. Cut and paste the code provided

Hope this helps.