Excel Sheet for Tracking Progress of Projects

[spectraflame]

Just wondering if anyone has any good examples of spreadsheets that are designed to track projects? I know that MS Project is designed to do this, but the organization that I am working for does not have MS Project money in the budget this year.

Thanks,
Matthew

 

[ammdumas]

Hey spectraflame,

What kinda stuff are you looking to track? I don't know any standard sheets out there (I'm proably not as good as you with excel either!), but I'm working on one myself which uses dates to track movement of documents. I'm also a project manager and might be able to give you some tips at the very least.

 

[spectraflame]

ammdumas,

I am looking to design something to track our utility projects like:

Construction of new Power Lines
New water line
new substations
and other large projects.

I have some basic ideas on paper, but I was just wondering if anyone else out there has encountered these issues and what they actually did to overcome them.

Thanks,
Matthew

 

[ammdumas]

Hmmm,

Well, my company probably isn;t the best example (a thousand spreadsheets when a database would work just fine!), but what about Access? Is that something that you have/is in the budget? MS Project is essentially a (SQL I think) database designed to do just what we're talking about.

As for what I'm doing, I'm linking a couple of spreadsheets and using formulae such as VLOOKUPS and validation to sort stuff. I also got help from TommyGun, to send out auto emails when certain dates are past and project items are overdue (in my case drawings and specs that were sent out for review and need to be sent back in a certain amount of time.).

Probably not much help , but I hope this gives you some ideas...

Austin (aka ammdumas).

 

[spectraflame]

I would be interested in the auto e-mail stuff. I may be able to incorporate that into something that we could use.

Thanks again,
Matthew

 

[ammdumas]

Compliments of onlyadrafter and TommyGun...

This cycles though rows of data and emails those lines was "Past Due" in column N. Adding ("X" & MY_ROWS).Value puts actual data in parts of your emails (subject, body, etc.) where X is the column where the stuff resides. VERY handy stuff. Hope you find it useful.

Austin.

Sub FIND_PAST_DUE()
For MY_ROWS = 1 To Range("N65536").End(xlUp).Row
If Range("n" & MY_ROWS).Value = "PAST Due" Then
'YOUR CODE HERE

Dim objOut As Object
Dim objTask As Object
Dim blnCrt As Boolean

On Error Resume Next
Set objOut = GetObject(, "Outlook.Application")
If objOut Is Nothing Then
Set objOut = CreateObject("Outlook.Application")
blnCrt = True
If objOut Is Nothing Then
MsgBox "Unable to start Outlook."
Exit Sub
End If
End If
On Error GoTo 0

On Error Resume Next
Set objTask = objOut.CreateItem(0)
If objTask Is Nothing Then
If blnCrt Then objOut.Quit
Set objOut = Nothing
MsgBox "Unable to create task."
Exit Sub
End If
On Error GoTo 0

With objTask
.Subject = "Test"
.Body = "This is a test email from excel"
.Recipients.Add ("email@domain.com")
.Send
End With

Set objTask = Nothing

If blnCrt = True Then objOut.Quit
Set objOut = Nothing
End If
Next MY_ROWS
End Sub