If you use Smartsheet to manage several project plans, you sometimes need to create a combined Gantt chart that reports on multiple projects at once:
OnePager Express can take several Excel exports from Smartsheet (one per project), and combine them into a single Gantt chart that summarizes multiple project plans. If you don't already have OnePager, download a free trial and follow these simple steps:
Log into Smartsheet and open your first project plan, which in this case is called the "Alpha Project". We recommend including Smartsheet's Row ID column in your Excel export. To do this, we'll right-click on the Task Name column in Smartsheet, and choose Insert Column Left:
When the Insert Column window appears, choose the Auto-Number/System for Row ID:
Before clicking OK, we want to make sure that the Row ID is unique across all of your different projects. By default, Row ID is a simple number, so it's likely that you'll have the same values in each of your plan. To make the Row ID unique, add your project name as a prefix. This way instead of having a Row ID of "001", it will be "Alpha-001" instead. This makes it much easier to combine several Smartsheet projects into a multi-project Gantt chart later.
With the Row ID set up to include your project name, click OK to see how those IDs look in Smartsheet:
If you plan on grouping/sorting your Gantt chart based on each subproject, or assigning each subproject a different color, it's also a good idea to have a Project Name column that clearly states which project each task belongs to. To do this, we'll right-click on any existing Smartsheet field and insert another new column. This time, it will be a Text/Number column called Project Name:
Once the Project Name column has been inserted, make sure that each row in your Smartsheet plan includes the project name, like this:
Last but not least, decide whether you want your multi-project Gantt chart to include every single row from all of your Smartsheet projects or just certain highlights. If you only want to include a few rows, insert a Checkbox column into Smartsheet called Add to OnePager and then use it to select the tasks to include and exclude from your Gantt chart:
"Project Alpha" is ready to export from Smartsheet to Excel. Go to File > Export > Export to Microsoft Excel:
Repeat these steps for all of your other Smartsheet projects that you want to combine into your multi-project Gantt chart. For example, if we have second project called "Project Beta", it should look similar to how we've already set up "Project Alpha", but with different values for Row ID and for Project Name:
Once all of the Smartsheet project plans have been exported to Excel, you should have a set of Excel files--one for each project:
- Open the first "Project Alpha" file, and then go to Excel's Add-Ins tab. Click the the OnePager Express button:
When OnePager's start screen appears, click NEW to build a new multi-project Gantt chart.
- On the import wizard, you'll see that "Project Alpha" has already been imported, but we need to add the other Smartsheet projects. To do this, click on Add/Remove:
When the Data Source Selection window appears, click the Add button and then BROWSE FILES to attach as many additional Smartsheet projects as you'd like. There is not a limit to the number of projects you can include in a OnePager Gantt chart.
When you've finished adding multiple projects, click OK
- In the Task Selection section, choose the Add to OnePager field that you set up as a checkbox column back in Smartsheet. This will filter your rows so that only important information makes it into your Gantt chart.
- Click Next. This screen lets you decide which columns from Smartsheet you want to use in your OnePager Gantt chart. Most of the time, OnePager will auto-detect the correct fields, but it's a good idea to make sure that OnePager's Unique ID is mapped to Smartsheet's "Row ID". We've also chosen to assign a different color to each Smartsheet project so it's easy to tell them apart.
Once things look good, click Create New Project View to build your Gantt chart.
- Initially, your OnePager chart will look like this:
It's a good start, but the tasks from the two different projects are jumbled together and we'll want to fix that.
- To group and sort the tasks based on their Smartsheet project name, we can create swimlanes in OnePager. Go to Home > Project View Properties > Rows/Swimlanes. Turn on swimlane groupings in OnePager and set the grouping to look at the Project Name field form Smartsheet:
For added emphasis, you can turn on the Show Swimlane Labels box to label each swimlane based on the project name as well.
- Click OK, and OnePager will automatically group and sort your Gantt chart based on the different projects that you've exported from Smartsheet:
Build your Smartsheet Multi-Project Gantt Chart today by downloading a free trial of OnePager Express.15-Day
Last Updated: July 26, 2019