Forum Discussion
Creating a tracker for monthly and random items
To answer your questions:
1. Q I can figure out how to filter the data (thanks to the filter button), but how do I display the data graphically, like in a pie chart? All I need is to be able to differentiate between priorities, and to show a percentage of work completed.
A. Let's worry about pie charts or other graphs later. The first task here is to develop a tracking system that is tracking what needs to be tracked. The graph that summarizes current status is the tail on that dog.
2. Q. How do I change the values of the completion status? I keep getting an error message saying that "A user has restricted values that can be entered into this cell."
A. That "user" was me. I used an Excel capability called Data Validation, which you'll find under the menu Data....Validation. You'll want to use data validation for any field like this that you'll later be using for reports; it guards against casual spelling errors and the like, as well as free-form entry.
The two small lists on the Business Table tab provide the accepted values for their respective fields. You can change them to whatever you want. You'll see I added a few beyond the "Complete" and "Incomplete" Right now there are five entries for that validation list; if you need no more than that, you can simply change the words that are there and that will serve you well. If you want to add more, you'll need to extend the range that carries the name "Status" .... the menu to do that is Insert....Name.....Define.
3. Q. I've noticed that the title and Priority will autofill with the SOP#, but the other fields won't. Not sure why that is.
A. This is on the "Tracker" tab. Look in those cells that are autofilling: there's a VLOOKUP formula that is retrieving (looking up) the information that goes along with that SOP#. In fact, on this new version I've added the "Assigned Deadline" date as a lookup. This is to illustrate the purpose of that business table on the BusTable tab...it's to give you ONE place to enter info that basically doesn't change, so you don't need to enter it each time there's an event to track in the life of that SOP. Think of Business Tables as references that are useful for data validation (noted above in Q&A 2) and things like this, deeper descriptors of what SOP# XXX is about. The "Tracker" tab is solely for tracking key events in the life of the SOP ....i.e, here's where your basic task of tracking is done.
So going further now, how you'll use the Tracker tab is to track status changes, going from "Initiated" through various draft stages, to penultimate review, to "Completed." I've suggested a few possible labels for those status markers, but you should change them to whatever your company actually uses. And how this could work (if it makes sense in your business context) is that each time there's a new stage reached on any given SOP, you'd enter in the yellow background fields the following:
- SOP# (on the basis of which Excel will lookup the next three columns)
- Date of the status change
- New status
- Any notes or comments
And you'd do this for each SOP each time there's a new stage in the process reached. This is what "tracking" means, I think....in that it tells you not only the current status for any given SOP, but also the history, the dates of each change, and (to the extent needed, via comments) specific issues or problems or learnings.
Take a look and see what you think. It's always subject to change, since it needs to meet your needs. This itself is still a project going through review.