Update priorities automatically

[JayCie]

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.

 

[sijpie]

JayCie,

forgot to say: make sure column I and J only contain numbers. Some of the cells I noticed had a space in them. If you do a delete of all the cells and then add some priorities it should be ok. Once you start of clean, the program won't allow the user to enter text, it will delete it.

cheers,
Jaap