Subcontractor needs help

John Crosby

I'm brand new to smartsheet and I'm trying to figure out how to best structure my sheets for managing multiple projects.  We're a construction subcontractor with 50-60 different projects in process at any given time - new ones added nearly everyday and other completed projects dropping off.

 

Do I need to set up a separate sheet for each project to track tasks, deadlines and completion, or is there way to set them all up in one sheet using the indent feature to make the tasks and subtasks for each project collapsible?

 

Also, is there a way to "archive" completed projects so i can get to them if necessary but otherwise don't see them?

 

Thanks.

Arvind Solanki

The indent versus new sheet issue is dependednt upon personal preferences.  If you have project sthat have comprehensive set of tasks that if expanded will force one to scroll and scroll and scroll to get to the next project, I would personally perfer different sheets and if needed some columns can be linked between the sheets.

 

if you keep it in one sheet, I would recomment a column with a title like "Done?" with drop-down values "Yes" & "No" or Red Button & Green Button.  All Reds/No are open, all Yes/Greens are done and then have a condition that makes all lines with greens get 'crossed out'. To make i even cleaner, create a report that reports on all that are not green.  In this report, you will only see the projects tha are in play.

Becky Smith

You might want to consider creating a Master Sheet.

 

First you can list on the Master your different project portfolio names in separate rows (EX:  Louisville KY Projects, Frankfurt KY Projects,  or by any other category/classification)

 

Then underneath the portfolio name row, insert a row for each individual project.

 

Link this row to the project actual sheet.  Pull in data from any of that project's columns that you wish by linking the cell in the master to the cell from the project.

 

if you want to show milestones, indent a row under each project name, write the milestone name on it and grab the milestone status from the project sheet.

 

Now individual sheets can live in a portfolio folder (Louisville KY Projects)

 

As you complete the project, use a naming convention and rename them with a preface, we put 3 z's in front to signify it's asleep; (Church of Christ Steeple Replace becomes zzz-Church of Christ Steeple Replace )

 

Once you rename it with zzz, you can open the Master Sheet, drag the project to a section on the Master Sheet called Archived Projects (or just delete its row).

 

You could also then move the renamed zzz  sheet into a folder called zzzProjects

 

Hope this helps

John Crosby

Very helpful comments.  Thanks.

Vince Roscelli

John,

My situation is virtually identical to yours -- also a subcontractor; also new to smartsheet; also searching for the best way to manage 10 to 20 active projects at a time -- adding new ones daily, dropping off the completed ones..

I will start by benefiting on the responses to your question.

Question for you have you tried to find/hire and expert to set up a structure for you and advise you on the most efficident way to implement? I need something "industrial stength here."

Thanks to everyone

Vince

 

 

Travis

Vince - if you are looking for help designing or building your sheets, check out our services page. One of the services we offer is Design Desk which will schedule half hour 1-on-1 sessions with you to build and design your sheets: http://www.smartsheet.com/services

YouReachedEd

John, I hope this isn't too late for you.  

 

The steps and crtical paths for all out projects are extremely similar. Gratefully, this lends itself to a separate sheet for each project and we find that works really well.  We took the time to figure out which fields are essential to managing our affairs and ensure all projects have those at a minimum.  Then we wrote a report to include those columns and add sheets as projects begin. When projects complete, all records become "100%" complete, so a filter in the report automatically drops those projects.  We do housekeeping once in a while to purge old projects from the report in the Report Builder.

Dan Davis

John, Vince,

I have been working the past 4-5 months to develop a project tracking/status system on Smartsheet.  So far I'm happy with the results and have deployed the system to my team.  It's too hard to share the system through a message like this and have you fully understand it, but I will try to cover some basics.  Hope this helps.

-  I view Smartsheet as a hybrid spreadsheet/data base.  It's not as complex as a data base program, and a whole lot easier to use.  A database program, like Microsoft Access, uses tables of data to produce reports.  In smartsheet your spreadsheets are those tables.  Reports can pull information from multiple spreadsheets.  My recommendation is to create your spreadsheets as tables of project data, then publish reports from that data.  Unlike others who have contributed to this discussion, I do not create a separate spreadsheet for each project.
-  Tables in a database need a common link.  Smartsheet uses the Primary column for this common link.  I use the project number as that link.
-  All of my projects that are expected to be completed in the current fiscal year (active projects) are on one master spreadsheet, I currently have 63 projects listed, and growing.  These projects are assigned a fixed project number. (Example: PW00060-160021)  

-  All of my wish list projects are on a second master spreadsheet.  I have 65 projects on the wish list. These projects are not assigned a fixed project number. (Example: PW00060-16xxxx).
-  To group projects in a report (for me it is by Program Titles) I created a spreadsheet for the different programs.  I currently have 52 +/- programs.  All of my projects are funded from a specific program.  I formatted my project numbers to include the program number.  (Example program number: PW00060).  I used color highlighting on the program rows to identify them separately as headings in the reports.
-  With this basic structure I can create reports that pull information from each of the three spreadsheets.  I configure the report to sort the data by the project number.  The report gives me a listing where the projects follow their respective program headings, and the wish list projects follow the active projects.
- when creating your spreadsheets try to think of all types of data you may want to use to sort the on a report.  You can pick and choose which pieces of data you want to be displayed on a particular report.
-  Smartsheet has made it easy to add to, and modify the spreadsheets.  Just start hacking away and build it as you go.

 

Some great advantages for Smartsheet are:  

-  Unlike a data base report, members of my team can enter data directly on the Smartsheet report I have shared to them and the updates are automatically made on the master spreadsheet.  This is great for my team to enter weekly project status info.

-  Unlike an Excel spreadsheet, my team can enter data on a Smartsheet spreadsheet simultaneously.  On Excel, two people cannot be updating the same spreadsheet at the same time without a substantial risk of data being overwritten.

-  I am able to create reports for my internal customers (Dept Managers) which are available to them 24/7 and they will see the updates my team members make in real time.  I don't have to publish weekly or monthly reports and issue them either electronically or in hard copy form, this is a big time saver for me.

 

I hope this helps you get started

Dan Davis

Vince Roscelli

 

Dan,

 

I am very intrigued by your idea of viewing Smartsheet as a hybrid of a spreadsheet/data base, and it may be just what I need.

 

However, I am still a novice where Smartsheet is concerned and will need some time to investigate / learn about using tables in Smartsheet – did not know they existed!  Also, I am currently engaged in a couple of firefights at the moment and the Smartsheet project is on hold and will be until later this year so you won’t hear anything from me for a while, but not for lack of interest.

 

Really appreciate you taking the time to share your thoughts and hope to apply them.

Vince

 

Dan Davis

Vince,

Sorry if I mesled you.  My reference to tables is just a reference to the similarity to a database.  The spreadsheets in Smartsheet would correlate to the tables in a database.  There are not tables in Smartsheet, just spreadsheets.  I guess the point I was trying to make is that the reporting feature in Smartsheet is the strength of the system as reporting is in a database.

I'm not a techy guy, and I made it work.  Good luck when you get back into it.

 

Dan