CODEWITHSUNDEEP
excelexcel-formula
TW_Doc
TW_Doc

Reputation: 49

Dynamically re-order numbered list in Excel

In Excel, is there a way to dynamically update the priority for a list of tasks? For example, if you have a column with priorities 1, 5, 3, 2, 4 and a user added a new row with a new task and assigns it a priority of 1, the list is updated with 2, 6, 4, 3, 5, 1?

Or is there some other (better?) way of doing this? I'd rather not have to sort the list and then manually change the numbering every time a new task is added or removed. Plus, I don't think the people who will use the spreadsheet will do that.

Upvotes: 2

Views: 11393

Answers (2)

Scheballs
Scheballs

Reputation: 532

I would lean towards a VBA answer but I found formulas and addition of columns to do what you need without macros. See image below

To keep this working from one addition to the next, after each new task is added you would have to copy your sorted task list in columns E:F, paste the values in A3, and delete the values in B2 and C2. Then you would need to drag your formulas down at the bottom of columns C:F since your list just got longer.

This is the biggest problem I can see doing it with in cell formulas, updating requires copy paste delete.

Dynamically Change a numbered list based on a Set Priority Formulas Dynamically Change a numbered list based on a Set Priority Results

Upvotes: 1

Declan_K
Declan_K

Reputation: 6826

Have a look at the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngPriorityList As Range
Dim lNewValue As Long
Dim myCell As Range

    If IsNumeric(Target.Value) Then 'Only run if the a number was entered

        Set rngPriorityList = ThisWorkbook.Names("priority").RefersToRange 'the named range for the task list

        If Not Intersect(Target, rngPriorityList) Is Nothing Then 'Only run the following in the cell being updated was in the priority list range
            For Each myCell In rngPriorityList.Cells 'Loop through the priority list range
                If myCell.Value = Target.Value _
                And myCell.Address <> Target.Address Then 'Finding cells with the same value, excluding the cell being changes
                    myCell.Value = myCell.Value + 1 'Increment the prioriry by 1
                End If
            Next myCell
        End If
    End If
End Sub

Upvotes: 0

Related Questions