Assign Priority in Excel - Please help

Thank you for looking into my issue and providing your feedback!

I have an excel tracking file which contains a lot of information for several people within my team. I'm looking for a way to assign priority based on a few inputs and I am unsure how best to accomplish this. The tracking file contains the following pertinent information:

Rows:
Each row represents a new line entry which will have it's own unique information.

Columns:
Assignee - could be assigned to 1 of 5 individuals
Criticality - Options include Critical, High, Medium, Low
Due Date - A date, either in the future or the past, in which the line item is to be completed
Priority - ??

What I'm looking for is a way for an individual to filter on their name so all of their tasks show up. Then based on the criticality and the due date, prioritize the work from 1 to 1+n so they know what needs to be worked in what order.

Criticality and due date go hand in hand as in critical need to be done first, then high, then medium, then low. Then within each of those, the ones with the oldest date needs to be done first. If there is an instance where a critical is due after a high, the High would get worked first. Additionally, if a new line item is added, the priority would adjust on all of the rows for that individual based on the same criteria.

Example 1: John wants to see what they need to work first. So John opens the tracker, filters on their name, and they can see what order they need to work the tasks.

Assignee	Criticality	Due Date	Priority
John	Critical	2/1/2023	1
John	Critical	3/1/2023	2
John	Low	4/1/2023	4
John	High	4/1/2023	3

Example 2: John has a new task that's added and the priorities adjust based on the new entry:

Assignee	Criticality	Due Date	Priority
John	Critical	2/1/2023	2
John	Critical	3/1/2023	3
John	Low	4/1/2023	5
John	High	4/1/2023	4
John	Critical	1/1/2023	1

I know I can use 'sort' and sort by criticality in the order of Critical, High, Medium, Low; Then by Due date on oldest to newest but I'd prefer a number in the priority rating column for easy filtering.

Maybe someone with better formula experience can find a formula solution, but just in case you're interested in a VBA solution, try out this automatic worksheet change approach. As soon as you enter the date in a row of data, the data will sort and assign the priority.

Please Login or Register  to view this content.