Excel - Sequence priorities and re-numbering dynamically

[tical2g]

Hi,

I'm hoping someone can help with an issue I have with a spreadsheet I manage at work.

I have a large excel sheet of change requests. Anything not currently planned in a release has a priority assigned to it, 1, 2, 3 .... etc

Note that the rows are not next to one another as its a large sheet and not all entries have a priority assigned.

When I get new changes added to the sheet they need priorities assigned against the current ones.

If say I have 3 new entries and they need to be set to priority 1, 8 and 12 I need a way that all the other priorities are dynamically updated.

Any ideas or suggestions are greatly appreciated, I only do very basic excel work so am currently at a loss how to do this?

Many thanks in advance.

 

[mintjulep]

What are the quantifiable factors that result in determination of priority?

 

[tical2g]

Things such as financial impact, business benefits etc ..

 

[mintjulep]

And the formula applied to financial impact, business benefits and etc is?

 

[SkipVought]

hi,

What you want, cannot be done with formulas.

a. the numbers you add, make incrementally SMALLER than the integer sequence numbers, like .9, 7.0 & 11.9. Add these rows at the bottom of the table

b. Then you sort your table on the sequence numbers.

c. Finally you renumber by increment of 1.

And the question arises; why do the numbers need to be whole numbers. You could EASILY doe whole numbers in another column, based on the REAL sequence numbers.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tical2g]

Mintjulep - it has no relevance to the issue, it is not a formula just done on judgement.

Skip - I got lost at the very end. When you sort do you +1 to every row? How does this sort the sequence out?

Thanks

 

[SkipVought]

A sort is just a sort.

Assuming that you numbered rows to insert into the sequence using decimal values, after having sorted, then renumber by incriments or 1. OR leave the sequencing column as is and have a column for incriment by one in a formula. The formula approch negates the renumbering each time.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]