Automating numbering of rows

[Alex]

I have rows that number based on the number of the previous row

A B C D
1 comp # Date Descrip Cust
2 100 1-1-04 broken Joe Blo
3 101 1-2-04 not working Pete
4 102 1-6-04 return Jane Do

Using a formula in A2 that if B3>0,B3=B2+1 I number the complaints for tracking. The problem is that if I want to sort by customer, I end up changing the numbers. Is there a way to correct this problem of even change the way I number the complaints so they will not change when sorted
I would welcome any input or opinions

 

[Frank Kabel]

Hi Alex

you can use ROW(). In your example insert the following in B2
=Row()+98
and copy this for all rows

HTH
Frank

 

[Alex]

Thanks for the reply Frank, but this still does not keep the number with the complaint when sorted. Any other ideas?

 

[Frank Kabel]

Hi Alex,

misunderstanding on my side. So you assign fix numbers (calculated by a
formula) to your complaints whcich should (of course) remain while
sorting.

IMHO this can't be done with an Excel formula. You'll need VBA:
1. Process the worksheet_change event
2. Evaluate if you have inserted a new complaint
3. Find the highest existing complaint number in column A
4. Insert a new complaint number for your new record

something like the following

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ID_value As Integer
Dim ID_cell As Range
If Target.Column = 2 Then
Set ID_cell = Range("A" & Target.Row)
ID_value = Application.WorksheetFunction.Max(Range("A1:A999")) + 1
If ID_cell.Value = "" Then
Application.EnableEvents = False
ID_cell.Value = ID_value
Application.EnableEvents = True
End If
End If
End Sub

Note: not much error checking done

HTH
Frank