Results 1 to 3 of 3

Thread: Calling all gurus... tricky request.

  1. #1

    Calling all gurus... tricky request.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,467
    Articles
    0
    Excel Version
    2010 on Xubuntu
    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 ?

  3. #3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •