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, etc.
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.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
-Mike Kapnisakis, Warner Bros
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.