Gantt Report Rollup

JoelN

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?

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/sheets/gHJPjvc392wwHHr24r4R554p2CmPQfFMRWqVGw91?view=gantt

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.

https://app.smartsheet.com/b/publish?EQBCT=adf8c6338d134e8ebe1f0100d7fd9fdd

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

JoelN

Thank you, I will give this a try.

jmyzk_cloudsmart_jp

@JoelN Glad I could help! Upvote for more!😁