Projects Possible

When using Excel I find that I will often times want to setup a worksheet that I can use as a template.  For example I might have some monthly sales or inventory tracking that I do and I want to use the same format each month,  but I want each month to be on a separate worksheet all within the same workbook.   I usually do this by creating the worksheet the way that I want it and then I just right click on the worksheet tab and change the name to something like ”SalesTemplate” or “InventoryTemplate” or ”PayrollTemplate”, whatever the data is.

Now when I need to start a new period of time or a new project I simply Add the worksheet(s) to my Excel workbook, go to the template worksheet, copy the entire thing and then paste the copy into the worksheet that I just added…. Very simple process. 

If you do this periodically and just with one worksheet it isn’t too bad to do it manually but if you find yourself needing to copy more than one and you are doing it regularly here is the snippet of a script that might give you some ideas of how you may automate this process.   This script is attached to a button called “Add Project” in my workbook and what it does is:

1. Prompts me for the next Sheet number to assign
2. Does a little bit of error checking
3. Copies my “Sheet Template” to a new worksheet and places it BEFORE Sheet99 in my Workbook
4. Changes the name of the worksheet to “Sheet##” using the number I entered above for ##
5. Places the number I entered in step 1 into the C6 cell
6. And then it protects the worksheet.
7. It then repeats the process using my “Info Template.

Sub NewWorksheets()
  Dim ans As String
  ans = InputBox(”What is the next sheet #?”, “New Sheet Number”, “”)

If ans = “” Then Exit Sub
  On Error Resume Next
  If Worksheets(”Sheet” & ans) Is Nothing Then
  ActiveSheet.Name = “Selector”
  ActiveSheet.Range(”D36″).Value = ans
  Sheets(”Sheet Template”).Copy Before:=Sheets(”Sheet99″)
  ActiveSheet.Name = “Sheet” & ans
  ActiveSheet.Range(”C6″).Value = ans
  ActiveSheet.Protect
  Sheets(”Info Template”).Copy Before:=Sheets(”Sheet99″)
  ActiveSheet.Name = “Info” & ans
  ActiveSheet.Range(”I3″).Value = ans
  ActiveSheet.Protect
 Else
  MsgBox “A sheet with that name already exists. Sheet not added.”
  Exit Sub
 End If
  On Error GoTo 0
End Sub

A very quick and painless process and I know that it is correct every time and that I have the values placed into the fields that I need them in.  Since I use this to keep track of individual project information I could be adding projects at any time and this script really streamlined the process for me.  You can also learn how to use HYPERLINKING to navigate through a workbook like this on my other post located HERE.

This is the first in a series of a few articles that I will be sharing on little tips and tricks in Microsoft Excel that I have accumulated or built over the last few months.  I’ve incorporated most of these into an Excel workbook or two to meet my specific needs.  I’m not really a Visual Basic for Applications programmer so I probably won’t be able to help out much in customizing…I just tweak things a bit here and there with help from online resources like Excel Forums. 

In this post, I want to show you how you can have a series of checkboxes in a column and when you “check” the checkbox it populates the cell directly to the right of the checkbox with the current date.   I use this in a project management spreadsheet where I have any number of projects all with common tasks that must be completed.

If you look at the spreadsheet above you will see that there are 3 columns… what you don’t see over to the far left in column A is the name of the task that I want to complete…  When I create a new project this portion of my spreadsheet is populated with a series of expected dates based on a set of known criteria.   I want to be able to come to this worksheet and not only quickly review a project and what needs to be completed but I want to be able to quickly update the “work” that was done on a project and have it give me the date that it was completed.   This is where my checkbox routine comes into play…  by simply clicking on the checkbox I can indicate the task is complete and it will make a permanent record of when it was completed… If I check it by mistake I can uncheck and it will also remove the date.

So how do I do this?

Sub Process_CheckBox()

Dim cBox As CheckBox
Dim LCol As Long
Dim LRow As Long
Dim Rng As Range

  LName = Application.Caller
  Set cBox = ActiveSheet.CheckBoxes(LName)

'Find row that checkbox resides in
  LCol = cBox.TopLeftCell.Column
  LRow = cBox.TopLeftCell.Row
  Set Rng = ActiveSheet.Cells(LRow + 1, LCol + 1)

'Change date in cell to the right of CheckBox, if checkbox is checked
If cBox.Value > 0 Then
  Rng.Value = Date

'Clear date in column B, if checkbox is unchecked
Else
  Rng.ClearContents
End If

End Sub

The key lines here are:

• Set Rng line which determines the row and column to place the date… You may tweak these to be +2, +3, whatever you need based on where and how you place yoru checkboxes in your spreadsheet and where and how you want your dates to appear.
• Rng.Value sets the date if there is a check in the checkbox
• Rng.ClearContents clears the date if the checkbox does not have a date

To use the routine you must open the Visual Basic Editor in Excel - Alt+F11, Click on Insert, Select Module and then Paste the Code above.   In your spreadsheet you would then right click on your checkbox and Assign a Macro to it… the macro is called Process_Checkbox.  Now every time you check the checkbox it will run this macro and it will insert the date.

As a project manager, I am always looking for great tools and utilities that will help me to become more productive and improve customer (internal and external) experience.

I know about the standard stuff that we use like Microsoft Project, Mindmapper, Excel, Outlook and the like.   What I would be interested in is hearing what you’ve found that you believe to be absolutely indispensable.  For example, I have posted reviews on an Outlook addin that I use called ClearContext IMS.  This tool helps me become a much more effective and productive email manager.    I’ve also posted about Excel and some of the things that I try to do with Excel to make me more productive.

 What do you do?  What little tricks have you found that help you out as you work on projects…  Share them with us!

There are a LOT of theories and ideas floating around to help with time management and productivity.  Classes and seminars are offered, books are published, and websites and blogs are posted all to try to help us better manage our lives.  For this author there has been no one method to help with time and productivity management.   In fact before I knew about these methods I was trying to come up with things on my own that many of these methods suggest.

In today’s posting I am going to give some insight on how I manage my projects using Microsoft Outlook, ClearContext’s IMS, Nelson’s Email Organizer (NEO) and Excel.   My goal has been to try to get everything I do that is work related consolidated into a single location and while Outlook is getting pretty close, my work environment requires some additional reporting structures that keep me away from my single trusted system.

For those of you familiar with Getting Things Done (GTD) by David Allen, or Total Workday Control (TWC)by Michael Linenberger it is all about changing the way that we work.  This can be as simple as the little things like grouping like things together and working on them all at the same time to much more complex and time consuming changes like rebuilding your workspace, developing filing systems, etc.   The key for me was to latch on to an idea or two and work with them, integrate them into your life and then add more once I had mastered the ideas I was working on.

When I started managing projects I found rather quickly that the medium that people wanted to use for communication was email.  I quickly went from an email box of 20-30 pieces of significant email every day to 200-300 pieces of email every day.  Combine that with customer face time and limited access to email and I soon found I was not keeping up.  I needed a solution that could help me manage and find things much easier than I had in the past.   The solution I found at the time was Nelson Email Organizer.  This is a great tool and one that I use to this day for email searches and correspondent management right from within Outlook.  NEO It was my first attempt at managing each piece of email in my Inbox, giving it a home, and knowing and being comfortable with the fact that I was going to be able to find it again if needed.   Another KEY feature of NEO for me was the ability to search across multiple mail stores.  Remember that 2GB limit that Outlook used to have?  My solution to that was to create separate Archives and move messages into those archives.   The down side to that is the messages were no longer search-able in Outlook unless I loaded each of the archives one at a time and performed the search.  Because NEO creates its own set of indexes that are super fast, this restriction was removed and I could keep years worth of email and search through 10s of thousands of messages in a matter of seconds.

NEO and Outlook worked well for me for quite awhile but NEO didn’t help me with managing tasks and so I went on the hunt once again for web solutions for project management.  Ultimately I stumbled across GTD (Getting Things Done) and TWC (Total Workday Control).  Take a look at the graphic.  The concept with these systems is that as messages come into your inbox you need to make a decision about them.  Ask yourself if you need to do something with this email… If not you probably just need to file it away as a reference or better yet if it isn’t going to be needed just delete it. (NEO would help me with this so this was nothing new.)  The other side of the equation however is more difficult to deal with.  If I need to do something with it, what is it that needs to be done, who should do it, when should it be done, etc… all start entering into the picture.   As you can see from the graphic there really are 3 choices… I do it now, I delegate it to someone else, or I hold onto it until a future date.  It was something that hit me as being so simple yet almost unbearable to manage.  That is where ClearContext’s IMS product entered the picture for me.  With IMS not only are the non-actionable items manageable but the actionable items become manageable as well and all linked to the appropriate projects and people.  I downloaded it, installed it and I’ve never looked back.  Keep in mind that I don’t claim to be a GTD or TWC guru and I don’t do all of the things that I’m supposed to do nor will I probably ever do them all.  I don’t even use all of the functionality that exists within ClearContext’s IMS product (yet).  That isn’t the point.  The point is I use what works for me and as I fine tune my process I may also find I use more of their concepts or in the case of the software their tools. 

The last tool that I have started using pretty extensively is Excel.  I find that I can use it to keep track of a lot of information in an organized form and have at my fingertips information about each project that I use on a day to day basis.  With Excel I am able to streamline common project tasks information on the items that are repeated for each project that I work on.  The members at Excelforum  have really helped me in this end goal by helping me to create scripts that help me keep track of tasks that are completed, scripts that help me to create new task spreadsheets with limited numbers of key-presses, and most recently with a script that helps me to create Outlook tasks for each and every item  of a project that I need to be tracking.

While my methods are not perfect… the goal is and has always been to increase my productivity and reducing the amount of work that I have to perform.   I’m always looking for great new ideas so if you have any to share please do so.   In the next few weeks I hope to share with you some of the scripts that I use in my Excel workbook.  I also encourage you to read my reviews on ClearContext or better yet go to their website and download the application and give it a try.

By my own admission I am NOT a Microsoft Excel expert.  I know enough about the application to make me dangerous. 

I use Excel pretty extensively in my day to day work activities, and in my continuing effort to streamline my day I decided that I needed to add some functionality to my project management spreadsheet.  This spreadsheet has really become my single point of information for all of the projects that I work on and I wanted to enhance it even further by integrating it with Outlook.

The integration that I had in mind was to read through the TASKs that I have detailed in my Excel worksheet and IF there was a due date to create a task for me in Outlook using this date as the due date and various other information from the spreadsheet as my subject.   I certainly didn’t have the expertise needed to complete this task on my own so I headed out on the Internet to find a solution.

Google is a great thing! Within a matter of minutes I had any number of resources available to me from my search terms about Creating Outlook Tasks from Excel.   Many of them included quoted scripts that gave me a head-start on what I wanted to do but ultimately none of them quite met my specific criteria.  I decided what I should probably do is simply pose the question to the experts in some of the forums that Google was pointing me to.

Now keep in mind that your mileage may vary at these sites.  I’m simply telling you of my experience.  I’m sure with the thousands and thousands of posts that each of these sites have that either would be a good place to get solutions.

I went to http://www.excelforum.com/to start with.    This particular site had helped me a number of months prior with a question I had about populating a cell with a date when I clicked on a check-box.  Within a matter of hours I was up and running with the script that I needed to perform this task.  My Outlook task on the other hand did not get the same type of response.   I was provided with virtually the same code that I had found doing Google searches and when it came to customizing that code my post sat unanswered.

A few weeks later I decided that I would try to reinvigorate the project and again get on my trek to higher productivity.  I Googled again and this time I found a post at http://www.ozgrid.com/forums-  It actually had a few Google hits that led me to believe there were people there that knew exactly what I was wanting to do.  I can tell you that on this site you need to heed the warnings of reading the RULES. And that the rules are Strictly enforced.  You have plenty of opportunity because they are part of the main page, you hear about it in the registration, and even when you are ready to post.

Read more »

In my job it is not uncommon for me to visit with customers and need to be able to access the functionality of Microsoft Office Word or Excel only to find that my customer does not own the Office Suite.   To handle this situation I can either get what I need and email it to myself so that I can use it on my laptop,  connect my laptop to their network and try to get it configured correctly, OR I can go to OpenOffice.org and download their open source (free) suite of applications that are compatible with virtually all that I would ever need.

 The suite of tools include:

• Writer - This is a Word Processor compatible with Word
• Calc - The spreadsheet compatible with Excel
• Impress- Presentation software that is like Powerpoint
• Draw - Draw diagrams with this one
• Base - Database application similar to Access
• Math - Equation/Formula solver

I can highly recommend them and for all but the most complicated of “OFFICE” documents or spreadsheets.  Compatability for me has never been an issue.