I have a spreadsheet with all the projects we have ranked by 2 managers by priority. I need to renumber the priorities as projects become more urgent or are completed and am looking for a way to do this automatically as our list of projects grows.
The relevant columns are I, J and L.
As we update column L (Status) to 'Completed', I need columns I and J to ‘move’ a project up or down in priority. For example, if we mark Manger 2’s no 1 priority to completed, we want the current 1 to be deleted and 2 to become 1, 3 to become 2 and so on in Manager 2's column and, if there is a ranking in Manager 1's column it needs to be deleted and the projects in this column need to updated in the same manner..
Also, if we change a project’s priority I need to update the remaining priorities up or down as necessary. For example, if we change priority 5 to become priority 3, we need the existing 3 and 4 to become 4 & 5. Or, if we change priority 2 to priority 5, we need to change 3, 4 and the existing 5 to 2, 3 & 4.
Can anyone assist with some code that would accomplish this? Any assistance is appreciated.
The relevant columns are I, J and L.
As we update column L (Status) to 'Completed', I need columns I and J to ‘move’ a project up or down in priority. For example, if we mark Manger 2’s no 1 priority to completed, we want the current 1 to be deleted and 2 to become 1, 3 to become 2 and so on in Manager 2's column and, if there is a ranking in Manager 1's column it needs to be deleted and the projects in this column need to updated in the same manner..
Also, if we change a project’s priority I need to update the remaining priorities up or down as necessary. For example, if we change priority 5 to become priority 3, we need the existing 3 and 4 to become 4 & 5. Or, if we change priority 2 to priority 5, we need to change 3, 4 and the existing 5 to 2, 3 & 4.
Can anyone assist with some code that would accomplish this? Any assistance is appreciated.