Automating numbering of rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi Alex

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

HTH
Frank
 
Thanks for the reply Frank, but this still does not keep the number with the complaint when sorted. Any other ideas?
 
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
 
Back
Top