Project Tracker
Managers of long projects with regular status meetings often use Excel to take notes, only to find that it becomes more and more unmanageable with each meeting. With many weekly updates on each of many items, it's a three-dimensional problem stuck in a two-dimensional sheet. Actions, owners and dates get lost in the text as updates accumulate in a single cell per item.
Project Tracker keeps Items and Notes in two similar Tables that are linked and stacked vertically in a split screen. Double-click events are used to select the related records, insert new records, and set status indicators. Right-click events set urgency and sort the tables. Faux control symbols and Conditional Formatting reflect status conditions including overdue, escalation, and closure, and show what remains to be updated.
Excel features: stacked and linked Tables; Split Window; "Faux Control" Symbols; Conditional Formatting; Row Group; Data Validation rules and messages; VBA: Worksheet event triggers; automated Window Split, Table Sort, Table Add rows, Conditional Formatting and more; contextual logic to perform different actions based on Target cell address.
Contents
Project Tracker.zip, with
- Project Tracker.xlsm (Excel Macro-Enabled Workbook), with sample data
- Project Tracker.xltm (Excel Macro-Enabled Template), blank
Usage
USAGE NOTES
Settings: Enter project title; double-click vertical double arrow to open the header row group to enter more project information, to view color and symbol legends, and to set date format and duration basis.
Items
Insert: double-click in Text column to insert new Item with next number; enter Text and Name
Select: double-click in Select column to select Item and related Notes
Delete: Ctrl- to delete table row; orphan Notes are flagged
Notes
Insert: double-click Items[Item] to create new Note with Name and today's date
Status: right-click Status to cycle Lo-Hi-None level; double-click Status to close or reopen Note
Delete: Ctrl- to delete table row
Monitor and maintain:
Insert new Note as above; this selects all Notes attached to the Item
While Item is selected, review and update Status and End of existing Notes
Set Status as above to escalate or close a Note
Right-click a column header to sort the table
TEMPLATE NOTESProject Tracker is most useful as a template. This allows you to save a blank version of the file which remains intact as you use it to create new files. Thus, you don't need to retrieve your last file and remove all the data to start a new one, just open the template and it will save as a workbook.
Project Tracker is provided as both Excel Macro-Enabled Template (Project Tracker.xltm) and Excel Macro-Enabled Workbook (Project Tracker.xlsm). This is so that you can see a filled example to learn how it works, and have the template for your own use. To use the template, first make it accessible.
Option 1: save the template file in your template folder, which you can find in Options, Alt-F,T,S, something like C:\Users\ (username) \OneDrive\Documents\Office\Templates.
Option 2: open the template, you'll see Project Tracker1 as the filename. Hit F12 (Save-As), remove the "1" in the name, and in Save as type:, select Excel Macro-Enabled Template. This selects your template folder automatically.
To use the template, File-New and navigate to personal templates, Alt-F,N,Y,3, it should be there. You can pin it to the top to make it easier to find next time.
To change the template, follow Option 2 above; open, edit, then Save-As and overwrite the current one. For example, you might want to save it with some data that is standard or similar across projects.