Reprioritizing a list of tasks automatically

[bluphoto]

I have a list of tasks numbered 1 to 10. they have differing priorities from 1 to 10, but the priority is NOT linked to the task number...
for example:

task priority
1______6
2______2
3______3
4______1
5_____10
6______8
7______4
8______9
9______5
10_____7



If I come along with task 11 and give it priority 5, then the task which had priority 5 now has p6; p6->p7, p7>p8 and so on. Similarly if I take task 2 which has p2 and change the priority to p8, then the tasks which had p3,p4,p5,p6,p7 & p8 all move up one priority point.

How do I get excel to manage these priorities for me?

thanks
Guy

[RedSummer]

Hi Guy,

What I've seen most people do is rank priority from High to Medium to Low, so that this isn't an issue. VBA code would make it so that you can enter a value in a cell that will change the values of all cells in that column. You may want to post in the VBA section.
Otherwise you are deleting and replacing formulas constantly every single time you update a priority of a task.

I have also seen excel users place "*"s in front of the task, the more ** the higher it will be placed when sorted. This is similar to the High, Medium, Low option.

Sorry I can't provide a quantitative formula approach. Hopefully someone else might say something more useful to you.

Red