We have a list of tasks:
- Many already done and flagged as “Complete”
- Many flagged as “Pending”
- About 10 tasks at a time ranked from do first = 1, to do last = 10.
- When 1 is moved to “Complete”, 2 becomes 1, 3 becomes 2, etc.
- Then one from the “Pending” group may be moved to 10 right away.... or not.
- Then we sort the list.
- This is done manually.
At times we need to switch the order where, for example:
- 9 gets higher priority and may become 2. Then 2 goes to 3, 3 to 4 etc.
- Another time we may bounce a “Pending” task to a priority level already set. So, if task “Pending” moves to 2, then old 2 goes to 3, 3 to 4,...10 goes to 11.
- Some times we move an assigned task, such as task 2 back to “Pending”, then 3 moves to 2, 4 to 3, etc.
We are looking to increase the ranked number of tasks from the current 10 to about 60. Doing any task number renumbering manually will turn your hair gray!
I would think that vba might be able to handle this via perhaps workbook selection change on the “Task Status” column (A) to see
- what change was made and then renumber tasks when needed (when a number was added, changed or removed),
- assuring we have consecutive numbering starting at 1
- (the max number is not set and only determined by however many tasks are currently numbered - today we have sometimes only 6 and other times 12, and “tomorrow” we may have 50 one time and the next 65),
- without duplicating task numbers,
- and by preserving the order of tasks.
Though sadly, i haven't got a clue nor was i able to find any bits here or online that might get me on the right track.
Assistance with this project would be much appreciated.