Calling all gurus... tricky request.

V

valdaliso

New member
Joined
Mar 5, 2013
Messages
1
Reaction score
0
Points
0
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
 
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
Click to expand...

If we change 101 to order number 4 then 101.. is bumped by 1 ?
 
Try this worksheet event code

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
 
You must log in or register to reply here.
Back
Top