Calling all gurus... tricky request.

[valdaliso]

Hi we are trying to maintain a schedule via Excel. Mainly the worksheet will house additional information in regards to the roll out, location, team required attendees, etc. However, the order can change at any given time and we wanted to use a vlookup with the information below.

At quick glance I thought Row-1 will be sufficient but they added a wrench in the end.

The question is, how can we keep this table sorted by QC #, and rearrange the “Order in Queue” automatically if we need to move a QC up or down in the queue? (i.e. if we change number 101 to order number 4 then 101,102,103,104,106 and 109 should be updated accordingly and bumped up by 1 in the queue order)... I hope this makes sense. Also, the launch date column should be static relative to the order in queue column—so those would need to update simultaneously.

Thanks, in advance!!!

QC #	Order in Queue	Launch date
101	7	4/8/2013
102	8	4/11/2013
103	5	4/1/2013
104	6	4/4/2013
105	3	3/25/2013
106	10	4/18/2013
107	4	3/28/2013
108	2	3/21/2013
109	9	4/15/2013
110	1	3/18/2013

[Pecoflyer]

Could you be a bit more precise please i.e.

if we change number 101 to order number 4 then 101,102,103,104,106 and 109 should be updated accordingly and bumped up by 1 in the queue order

If we change 101 to order number 4 then 101.. is bumped by 1 ?

[Bob Phillips]

Try this worksheet event code

Option Explicit

Private prevVal As Variant


Private Sub Worksheet_Change(ByVal Target As Range)
Dim minVal As Long
Dim maxVal As Long
Dim lastrow As Long
Dim inc As Long
Dim i As Long


    On Error GoTo ws_exit
    
    Application.EnableEvents = False
    
    If Target.Count = 1 Then
    
        If Target.Column = 2 Then
        
            If Target.Value < prevVal Then
            
                minVal = Target.Value
                maxVal = prevVal
                inc = 1
            Else
            
                minVal = prevVal
                maxVal = Target.Value
                inc = -1
            End If
            
            lastrow = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row
            For i = 2 To lastrow
            
                If i <> Target.Row Then
                
                    If Me.Cells(i, "B").Value >= minVal And Me.Cells(i, "B").Value <= maxVal Then
            
                        Me.Cells(i, "B").Value = Me.Cells(i, "B").Value + inc
                    End If
                End If
            Next i
        End If
    End If
    
ws_exit:
    Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count = 1 Then
    
        If Target.Column = 2 Then prevVal = Target.Value
    End If
End Sub