Create a Milestone Report for Resource Names

Pamela Wagner

I have a project intake sheet that has a column for department resources where you choose the people who work will work on the project so this cell can have from 1 to 12 names in it. I need a report that will pull the names out of that column and note the project that they are on ala a gantt. So instead of seeing a list of projects on the left of the chart and the gantt with the timeline on the right, I need a list of resource names on the left and the timeline of each project on the right (or just add the resource names to the chart but individually so the chart will be much longer).

Current Project List Report:

New Report Sample:

Not sure how to get the report wizard to do this. Thinking I need another sheet that will pull the resource names out of that cell and then add in the project names and start finish for each project, then I can create a Gantt type chart that you see above. Not sure how to create that initial sheet/report though.

Genevieve P.

Hi Pamela,

I actually think the easiest way to do this would be to create multiple reports, one for each person. You would adjust the "Who?" section of the Report criteria to look for a specific person in that multi-select list. This would thing bring in all rows associated with their name, whether that's selected with other people or on their own.

Then you can duplicate the Report with save as new (see here) and simply adjust who you're looking for each time.

If you want all of this to be visible in one view, you can then embed each Report into a Dashboard using the Report Widget, one after the other, specifying who each Report is for (as the multi-select contact column will show the other names as well.)

Here's an example of what I'm picturing:

Let me know if you think this will work for you!

Cheers,

Genevieve

Pamela Wagner

@Genevieve P this is super close! I've tried it and it works and I get the concept. Is there anyway to pull out just the particular Resource's name into that Assigned To column? That would make it cleaner. I have a report where the creator added in a helper column but not sure why it's there:

I am not sure how he got the resources pulled out of the master sheet where it has multiple names per cell. This list then populates another report where you see the number of projects coming out to the right. I'm mostly interested in how he got the names pulled out and it that can be used somehow in what I'm trying to do.

Genevieve P.

Hi Pamela,

The Report will show the entire Assigned To column, it won't be able to only display one name in the cell for that column. In my example I added the person's name to the title of the Report widget to try and call out which specific person it relates to.

For your report above, this specific helper column looks like it was used to indicate which rows should be pulled in - since it was a criteria for the Report it was auto-added in to the view, but you can hide it. To do so, right-click on the name of the column and choosing the "hide" option.

It looks like this Report is pulled from a sheet which was specifically built for cross-sheet references (the COUNTIFS formula), and that the names were typed in manually, which is how you have them separately. I would suggest talking to the creator if you want more details, as I'm only guessing based on the screen capture. 🙂

Hope this helps!

Genevieve P.

Hi Pamela,

I actually think the easiest way to do this would be to create multiple reports, one for each person. You would adjust the "Who?" section of the Report criteria to look for a specific person in that multi-select list. This would thing bring in all rows associated with their name, whether that's selected with other people or on their own.

Then you can duplicate the Report with save as new (see here) and simply adjust who you're looking for each time.

If you want all of this to be visible in one view, you can then embed each Report into a Dashboard using the Report Widget, one after the other, specifying who each Report is for (as the multi-select contact column will show the other names as well.)

Here's an example of what I'm picturing:

Let me know if you think this will work for you!

Cheers,

Genevieve

Pamela Wagner

@Genevieve P this is super close! I've tried it and it works and I get the concept. Is there anyway to pull out just the particular Resource's name into that Assigned To column? That would make it cleaner. I have a report where the creator added in a helper column but not sure why it's there:

I am not sure how he got the resources pulled out of the master sheet where it has multiple names per cell. This list then populates another report where you see the number of projects coming out to the right. I'm mostly interested in how he got the names pulled out and it that can be used somehow in what I'm trying to do.

Genevieve P.

Hi Pamela,

The Report will show the entire Assigned To column, it won't be able to only display one name in the cell for that column. In my example I added the person's name to the title of the Report widget to try and call out which specific person it relates to.

For your report above, this specific helper column looks like it was used to indicate which rows should be pulled in - since it was a criteria for the Report it was auto-added in to the view, but you can hide it. To do so, right-click on the name of the column and choosing the "hide" option.

It looks like this Report is pulled from a sheet which was specifically built for cross-sheet references (the COUNTIFS formula), and that the names were typed in manually, which is how you have them separately. I would suggest talking to the creator if you want more details, as I'm only guessing based on the screen capture. 🙂

Hope this helps!

Pamela Wagner

Thanks @Genevieve P ! This helped a lot!

Genevieve P.

No problem!