Gantt Report Rollup
I'm trying to look at high-level resource utilization across multiple sheets and projects. I have a report that pulls the info by individual tasks, but ideally the Gantt view would show the full timeline of those tasks combined. I haven't been able to figure this out, any ideas?
Answers
-
Hi @JoelN
The following demo sheet rolls up the Start and End dates for each assigned project.
For this purpose, we have a sheet called Rollup by Assigned to Sheet, as shown in the second image. This sheet rolls up the Start and End dates for each "Assigned to" based on the project sheet's start and end date data.
You can create a report like the image below by combining the project sheet and this sheet in the report with the proper setting.
Rollup by Assigned to Sheet
This sheet should first be placed [No] as a helper column, with 1 to 10 (any number). The [No] is used as the row_index of the INDEX to get the Distinct [Assinged to] from the project sheet with the following formula.
[Assigned To] =IFERROR(INDEX(DISTINCT({Task Tracker : Assigned To}), No@row), "")
Then, using the [Assinged To] Values, the following formula will get the rollup Start and End date.
[Start Date] =IF(ISBLANK([Assigned To]@row), "", MIN(COLLECT({Task Tracker Range : Start Date}, {Task Tracker : Assigned To}, Task@row)))
[End Date] =IF(ISBLANK([Assigned To]@row), "", MAX(COLLECT({Task Tracker Range : End Date}, {Task Tracker : Assigned To}, Task@row)))
Note: You need to make those formulas cell formulas. Otherwise, the Gantt bar will not be shown.
If you have "multiple sheets and projects", this formula becomes more complicated and does not scale well, as you have to create cross-sheet-reference ranges for each sheet. So, this method best works if you have a master project sheet that combines all the data from individual project sheets.
Report setting
The report has the project sheet and rollup sheet as the source sheet.
Filter setting:
Groups setting:
We have a [Parent] helper column and a checkbox column on both sheets. The column is used to put the parent row at the top, Diana Kennedy, in this example.
Sorts setting:
Sort by Start date Oldest to Newest put task according to the Start Date. (End Date sort is not required, but the setting below puts the one End earlier if multiple tasks start on the same date.)
Project Sheet
https://app.smartsheet.com/b/publish?EQBCT=690cb7e13e5147b9bfdfe3f0f0d381fe
-
Thank you, I will give this a try.
-
@JoelN Glad I could help! Upvote for more!😁
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives