My end goal is to have a task-list in excel which is automatically prioritized (and/or colour-coded) based on the following four criteria:
UACT - Urgency, Authority, Consequence, Time (duration)
Urgent (higher priority) = Due date is within the next five business days
Authority (higher priority) = Task has been assigned by or fulfills promise made to a supervisor(s)
Consequence (higher priority) = Omission of task is not recoverable and would jeopardize outcome achievement
Time (higher priority) = Task can be completed in <1 hr
Urgency is the only criteria that is not fixed (obviously urgency changes on a daily basis as we move closer to deadlines that at one time were more than five days in the future)
Authority, Consequence and Time are all fixed responses that only need to be identified once (A & C are yes/no responses; Time CAN be a yes/no or can be based on a specific duration of time estimated for task completion).
I've attached the excel file that I'm currently working with which does this prioritization for me. I'll use the excel spreadsheet at the start of each day to give me a good idea of what my priorities are for that day/week but will be using Outlook throughout the day as new e-mails/tasks are assigned/updated/completed
What I would like to have happen, is my Outlook tasks sync with this spreadsheet so that when I open the spreadsheet, it automatically (or at the touch of a button via a macro, if possible) adds any new tasks to the list or marks any completed tasks as completed (but if new tasks in outlook are added and completed before I open the excel sheet, I do not want "completed" tasks to be ADDED to excel -- just mark the ones that are already in excel as completed -- that way, if I delete them, it doesn't re-add them).
Does this make sense?
I have added user-defined fields to outlook to identify Authority, Consequence and Time as TRUE or FALSE (via yes/no set to view as an icon in my task list) so that when these tasks are exported, the data is already there.
I CANNOT add to Outlook the following columns (which I would like to have automatically fill in given the information that comes from Outlook):
"Due in" column which calculates the number of business days left (or overdue) for each task based on today's date: =NETWORKDAYS(TODAY(),[@Deadline])
"UACT Priority" which calculates the number of criteria satisfied (0-4): =SUM(IF([@[Due In:]]<=5,1,0),IF([@Authority]="TRUE",1,0),IF([@Consequence]="TRUE",1,0),IF([@[Time (Duration)]]="TRUE",1,0))
Is there some way to add the Outlook task with the relevant data to this excel sheet that will fill in the other two columns automatically? I would also like to include the Outlook Categories and Assigned-To columns but I wasn't able to export that information to export; I also don't know how to get the user-defined fields to export to excel/csv.
As an additional FYI, I have this list sorting first by the UACT priority (0-4 with 4 at the top) and then by deadline.
Thank you for your help!!