You are here

Home

Project Manager reminder / to do list spreadsheet

Fri, 2018-02-16 22:09 — Michael Dew
 
Designed for Windows computers only, not Macs.
 
Security issues
The spreadsheet was developed using macros (programmed in visual basic) and in order for the spreadsheet to operate your security settings in Microsoft Excel must be set to allow macros to run.  To enable macros in Excel 2010 (similar procedures will apply in other versions of Excel) click:
  • File (i.e. the menu on the top left)
  • Options
  • Trust Centre
  • Trust Centre Settings
  • Macro settings
  • Enable all macros
Macros can pose a security risk because they make your computer perform certain steps automatically, but are typically only dangerous when made to be that way by a computer programmer with a malicious intention – hence the warning when you change the security settings in Excel. However, the macros available in the spreadsheet available for download are made with good intentions and should not harm your computer.
 
Users downloading and using this spreadsheet do so entirely at their own risk and agree to accept all risks associated with using the spreadsheet. 
 
_____________________________________________________________________________
 
Overview of the spreadsheet
The spreadsheet available for download allows one to sort, and bring forward / postpone reminders, allowing the user to add columns with content to describe each reminder. There are three mandatory columns for each reminder:
  • Date = The date the reminder comes date.
  • Day = The day of the week of the Date.
  • Time status = The colour coded cell that indicates the status of the reminder as past due, due next week, etc.
The working zone of the “Active!!! Tab” (i.e. the tab listing currently active reminders) occupies columns A to Z and up to row 5,000 i.e. that is the zone which is sorted when the sort macros are used. Therefore, with just 3 columns with fixed name users can name the remaining 23 columns anything they wish. This provides much greater flexibility than the original lawyer-focused reminder spreadsheet.
 
Currently outstanding reminders are stored on the Active!!! Tab, and as reminders are completed they are sent to the Completed!!! tab.
 
Users should not edit any of the cells (or worksheet names) ending in “!!!”, these are landmarks used by the macros and editing those cell names or worksheet names will prevent the macros from working. 
 
The cells in the Time Status!!! column contain a formula which determines the text shown in that column e.g. “Within 1 week”, “1 Week to 1 Month”, etc. The formula used is self-contained (i.e. is not modified by or involved in operation of the macros) and does not reference any cells apart from the Date!!! column. Therefore, that formula can be edited by the user e.g. to change the time periods that apply, or the text that is displayed for each time period. After editing the formula in the Time status!!! column be sure to copy that formula down through the rest of the rows in the Time status!!! column so that the same formula applies throughout the spreadsheet, and also applies to new reminders created in the future (i.e. the macro that creates new reminders uses the formula from the Time status!!! column of the neighbouring row.
 
Conditional formatting (available through the Styles Group on the Home tab of the ribbon) based on cell contents is used to determine the colour of the cells in the Time status!!! column, and that conditional formatting can be edited by the user as per the normal conditional formatting feature of Excel.
 
 
Functionality of buttons on the Active!!! tab
The following buttons on the Active!!! tab perform the following functions.
  1. New Item = Adds a new item to the list of active reminders, and when doing so prompts the user for the number of days into the future the reminder should be set for. It allows the user to use a date picker form to choose a date if desired. Right after using the New Item button the user can click the “Duplicate item in column” button to pull previously existing notations into the newly created item; see further explanation below.  
  2. Duplicate item in column = Used to pull into the currently selected cell a copy of text in any other cell in the same column. This button can be used when creating a new reminder e.g. if the user has a column named “File name”, then an existing file name in that column can be added to the row containing the new item by clicking on the cell (in the “File name”) column to contain the new text, and then running this macro, which will present a list box containing a deduplicated alphabetical list of all pre-existing items in the “File name” column, thus allowing the user to select a pre-existing file name to enter onto the current cell. (Note that “File name” is just an example, having a column named that is not mandatory – the only mandatory columns on each of the Active sheet and the Completed sheet are Time status!!!, Day!!!, and Date!!!).  
  3. Duplicate entire row = This duplicates the entire row selected at the time the button is clicked. This may be used to create a duplicate of a reminder and then the date of the duplicate can be changed so that the same reminder comes due at two different times (i.e. to add an extra reminder for safety a long way out from the actual due date). Alternatively, users may wish to create a duplicate and then edit just the description of the reminder to create a different reminder but with the same project / client / contact person /  etc. details.
  4. Modify Date = Use this button to change the date that the reminder will come due. This macro is used, for example, to push reminders off further into the future.
  5. Sort = Allows sorting of the data on the Active!!! tab according to any of columns A to Z. The range sorted is columns A to Z and rows 6 to 5000. The data is sorted by the column that is selected prior to the Sort!!! button being clicked.
  6. Sort & Go To = Sorts the contents of the spreadsheet by the column selected before the button is clicked, and then presents a form that allows the user to select a value in the column to jump to. This macro can be used to group together and jump to all items with the same notation (e.g. “File Name” if the user has created such column heading).
  7. Display / hide columns = Allows users to easily hide and display certain of columns A to Z.
  8. Move to completed = Moves the selected item to the Completed!!! Tab i.e. once the task the reminder relates to is completed.
 
Caution regarding editing column names
Note that the flexibility in being able to rename 23 of the 26 columns from A to Z on the Active!!! sheet causes complications when moving items from the Active!!! sheet to the Completed!!! Sheet after changing column headings. For example, if the user changes the column headings on the Active!!! sheet after already having sent items to the Completed!!! sheet, then items subsequently sent to the Completed!!! sheet will not fit neatly under the existing headings on the Completed!!! sheet.
 
To prevent data being written into mismatched columns, the “Move to completed” macro checks if the headings currently on the Active!!! sheet match those on the Completed!!! sheet, and if they do not match the macro prompts the user to confirm whether to proceed. If the user does proceed then the macro will write the current headings on the Active!!! sheet into the bottom of the Completed!!! sheet before copying the current item from the Active!!! sheet to the Completed!!! sheet. This will result in the data on the Completed!!! sheet being a bit less usable (i.e. with column headings changing part way down list sorting may disrupt the data), but that is a consequence of allowing flexibility in column naming on the Active!!! sheet.
 
When changing column headings on the Active!!! sheet, consider saving the spreadsheet as a new version and deleting previous content on the Completed!!! sheet. Doing this will ensure that new data added to the Completed!!! sheet can be usefully sorted, and the old data will still be available in the previous version of the file.
 
Additional points regarding the “Move to completed” button and the Completed!!! tab
Note that the “Move to completed” macro will not work if the column heading “Date!!!” is not already on the Completed!!! sheet. This is because the Date!!! column is important for sorting the data on the Completed!!! sheet, and is used by the macro as a guide as to where to paste the data being brought in from the Active!!! sheet. 
 
Therefore, when first using the spreadsheet and before sending any items to the Completed!!! sheet it is advisable to define the column headings you want to use on the Active!!! tab and then copy and paste that row of column headings from the Active!!! sheet over to the Completed!!! sheet. 
 
The “Move to completed” macro writes the date the item was moved to the Completed!!! tab into column AA of the Completed!!! tab. This allows the user to keep a record of when the to do item was moved to the Completed!!! sheet.
 
Need more sophisticated sorting?
If you wish to sort the data by one column and then sub sort by a second column, you can do so as follows (i.e. using the built in “Custom Sort” tool in Excel):
  1. Click on the cell containing the upper left most heading for your data set (perhaps the Time status!!! column).
  2. Press Ctrl – Shift – End to select the entire data set.
  3. In the “Editing” group on the Home tab of the Ribbon click on “Sort & Filter”, and then click on “Custom Sort”
 
 
  1. In the Custom Sort dialog that appears click the “My data has headers” checkbox and then use the “Sort by” dropdown list to specify which column to first sort by, and then click the “Add Level” button to add a sort level and allow you to define the column to sub sort by. Additional levels can be added as needed.
 
Want to email reminders to others?
It is acknowledged that some users will want to send out emails to others reminding them of tasks needing completion, seeking updates about ongoing tasks, etc.
 
Writing data from Excel to an email client is possible but complications arise depending on what data the user wants to write, what email client (e.g. Outlook?) the user is using etc.
 
Consider the following method for “quickly” adding data to emails if Outlook is used as an email client:
  1. Put the email addresses the reminder will routinely be sent to in a suitable column. You can enter multiple email addresses in the same cell, separated by semi colons.
  2. Turn on the clipboard pane in Excel by clicking on the expansion button on the bottom right corner of the Clipboard group on the Home tab of the Ribbon in Excel.  
 
Turning on the clipboard pane will show not just the item currently on the clipboard, but a list of recent items on the clipboard.
  1. Copy to the clipboard (by selecting cells and then pressing Ctrl-C) the various batches of data you want to appear in the email. You will see a list of copied items appearing in the clipboard pane in Excel as you copy them to the clipboard.
  2. In Outlook, compose a new message and turn on the Clipboard pane in that message (on the “Clipboard” group on the “Message” tab on the Ribbon in the new Outlook message i.e. similar to how the clipboard pane was displayed in Excel).
  3. By clicking in fields in the email (e.g. “To”, “Subject”, Message body, etc.) and then sequentially clicking on items listed in the clipboard pane in Outlook (which will be a duplicate of the clipboard pane in Excel) you can quickly enter data into the new email in Outlook.
 
 

Copyright © 2007-2023 Legaltree Publishing Inc.

Legaltree was founded by Michael Dew, a Vancouver lawyer and is owned and operated by Legaltree Publishing Inc. 

Waiver of liability: All content on www.legaltree.ca is for information only and must not be relied on in any way. No legal advice is sought by browsing this site, and none is given. All links are provided for convenience only and no sites linked to are endorsed.