Move up the queue when modified

[electronictokwa]

Hi guys! I hope you can help me out

I have a table below with test names and modifiers (yes or no). What I need is for the bottom name to go up the queue every time it is modified by entering a value in cell next to it. It is even possible? Any ideas?

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>


[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Name[/TH]
[TH]Modified (Y/N)[/TH]
[/TR]
[TR]
[TD]Test 1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Test 2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Test 3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Test 4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Test 5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[electronictokwa]

You are welcome!

M.
ps: for further questions you can open a new thread, if you prefer.

Hi Marcelo! I had to slightly tweak your code to move the timestamps up the queue together with the names:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vRng As Variant
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B6")) Is Nothing Then
        Application.EnableEvents = False
        vRng = Range("A2:C5").Value
        [B]Range("C6").Value = Now[/B]
        Range("A2:C2").Value = Range("A6:C6").Value
        Range("A3:C6").Value = vRng
        Application.EnableEvents = True
        
    End If
    
End Sub

Got another question related to the original post. There will be two tables instead of one. What's going to happen is that the bottom name in each table when modified will go to the next table's queue; so the main idea is for each name to go through each table's queue. Is this something that can be done?

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Name</th><th>Modified</th><th>Time Modified</th><th> </th><th>Name</th><th>Modified</th><th>Time Modified</th></tr> <tr><td>Test 1</td><td> </td><td> </td><td> </td><td>Test 6</td><td> </td><td> </td></tr> <tr><td>Test 2</td><td> </td><td> </td><td> </td><td>Test 7</td><td> </td><td> </td></tr> <tr><td>Test 3</td><td> </td><td> </td><td> </td><td>Test 8</td><td> </td><td> </td></tr> <tr><td>Test 4</td><td> </td><td> </td><td> </td><td>Test 9</td><td> </td><td> </td></tr> <tr><td>Test 5</td><td> </td><td> </td><td> </td><td>Test 10</td><td> </td><td></td></tr></table>

 

[Marcelo Branco]

Could you provide an example Before - After the modification?

M.

 

[electronictokwa]

Here is an example. Test 5 is modified, goes to the next table. When the bottom (Test 10) is modified, that's the time it will go to the first table's queue.

Before:

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Name</th><th>Modified</th><th>Time Modified</th><th> </th><th>Name</th><th>Modified</th><th>Time Modified</th></tr> <tr><td>Test 1</td><td> </td><td> </td><td> </td><td>Test 6</td><td> </td><td> </td></tr> <tr><td>Test 2</td><td> </td><td> </td><td> </td><td>Test 7</td><td> </td><td> </td></tr> <tr><td>Test 3</td><td> </td><td> </td><td> </td><td>Test 8</td><td> </td><td> </td></tr> <tr><td>Test 4</td><td> </td><td> </td><td> </td><td>Test 9</td><td> </td><td> </td></tr> <tr><td>Test 5</td><td> </td><td> </td><td> </td><td>Test 10</td><td> </td><td></td></tr></table>


After:

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Name</th><th>Modified</th><th>Time Modified</th><th> </th><th>Name</th><th>Modified</th><th>Time Modified</th></tr> <tr><td>Test 1</td><td> </td><td> </td><td> </td><td>Test 5</td><td> </td><td> </td></tr> <tr><td>Test 2</td><td> </td><td> </td><td> </td><td>Test 6</td><td> </td><td> </td></tr> <tr><td>Test 3</td><td> </td><td> </td><td> </td><td>Test 7</td><td> </td><td> </td></tr> <tr><td>Test 4</td><td> </td><td> </td><td> </td><td>Test 8</td><td> </td><td> </td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td>Test 9</td><td> </td><td> </td></tr> <tr><td> </td><td> </td><td> </td><td> </td><td>Test 10</td><td> </td><td></td></tr></table>

 

[Marcelo Branco]

I'm busy now. I'll take a look later.

M.

 

[electronictokwa]

I'm busy now. I'll take a look later.

M.

Hey no problem at all. I appreciate all the help. Thanks so much!

 

[Marcelo Branco]

I'm thinking about it...

It's a completely different question because the cell that could be updated isn't fixed anymore - previously only cell B6 had to be checked now we have different cells to consider.

A couple of questions:
1. Are the cells that can be updated always the last of each column?
2. Could you have more than 10 tests?
3. What is the purpose of this?

M.

 

[electronictokwa]

I'm thinking about it...

It's a completely different question because the cell that could be updated isn't fixed anymore - previously only cell B6 had to be checked now we have different cells to consider.

A couple of questions:
1. Are the cells that can be updated always the last of each column?
2. Could you have more than 10 tests?
3. What is the purpose of this?

M.

1. Yes, it is going to be the last cell of each column.
2. For now, there are around 25 names, divided into two tables.
3. This is going to be a ticket assignment table. Two people will be assigning tickets. The main gaol is to avoid double assignment per name, that's why I am thinking of creating two tables, one per assigner, and each name can only exist in one table, going through the rotation.

If you can think of a much better way I am very open to suggestions. Again, I am very thankful for the help

 

[Marcelo Branco]

hmm...i'm not following you
The lists already exist? Each assigner has a complete list?
I'm lost

M.

 

[electronictokwa]

hmm...i'm not following you
The lists already exist? Each assigner has a complete list?
I'm lost

M.

Sorry for the confusion. Two assigners will be working on two separate tables (for this example, we can say 12 in the first table, and 13 in the next table = 25 names total).

 

[Marcelo Branco]

Assuming the first list in columns A, B, C, the second in columns E, F, G and headers in row 1, see if this is OK

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cellB As Range, cellF As Range, ans As Long
    
    If Target.Count > 1 Then Exit Sub
    Set cellB = Range("B1").End(xlDown)
    Set cellF = Range("F1").End(xlDown)
    
    If Target.Address = cellB.Address Then
        If cellB.Offset(, 1) = "" Then cellB.Offset(, 1) = Now: Exit Sub 'New entry
        Application.EnableEvents = False
        If Range("E2") = "" Then
            Range(cellB.Offset(, -1), cellB.Offset(, 1)).Cut Range("E2")
            Range("G2") = Now
        Else
            Range("E2", Range("E1").End(xlDown).Offset(, 2)).Cut Range("E3")
            Range(cellB.Offset(, -1), cellB.Offset(, 1)).Cut Range("E2")
            Range("G2") = Now
        End If
        Application.EnableEvents = True
    End If
    
    If Target.Address = cellF.Address Then
        If cellF.Offset(, 1) = "" Then cellF.Offset(, 1) = Now: Exit Sub 'New entry
        Application.EnableEvents = False
        If Range("A2") = "" Then
            Range(cellF.Offset(, -1), cellF.Offset(, 1)).Cut Range("A2")
            Range("C2") = Now
        Else
            Range("A2", Range("A1").End(xlDown).Offset(, 2)).Cut Range("A3")
            Range(cellF.Offset(, -1), cellF.Offset(, 1)).Cut Range("A2")
            Range("C2") = Now
        End If
        Application.EnableEvents = True
    End If
    
End Sub

M.