Visualizing relationship of similar tasks across many different Projects

R

RogerC

Well-known Member
Joined
Mar 25, 2002
Messages
537
I have a schedule of Project level activities that encompasses tasks spanning multiple separate projects. Each Project is individually implemented at a different business location around the globe. However, any individual location may be impacted by the schedule of more than one project in the list at any given time.

At the Program Management level, I need to graphically visualize the relationships of all tasks across all projects. For example, I need to understand the extent to which any single location is being impacted by the tasks of multiple projects over the time period from start to finish of all projects.

My goal is to graphically visualize the magnitude of effort required at the sites when multiple projects are in work. Basically, I need a graphic timeline of all Tasks over the three year period shown by Site, Project and Task Durations (days).

A highly simplified version of my combined Task Schedule data for 4 Projects being deployed across 3 Sites might look like this:

Excel 2013/2016
ABCDE
Survey
Approval
Budget
Install
Go Live

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: center"]Site[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: center"]Project[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: center"]Task[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: center"]Start[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F8CBAD]#F8CBAD[/URL] , align: center"]Finish[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]New York[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] "]Survey[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]07/1/17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]08/11/17[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]New York[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] "]Approval[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]09/5/17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]10/5/17[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]New York[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] "]Budget[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]02/1/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]04/30/18[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]New York[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] "]Install[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]06/1/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]09/17/18[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]New York[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] "]Go Live[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]09/20/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]09/22/18[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]New York[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] , align: center"]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] "]Survey[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] , align: center"]09/10/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] , align: center"]10/1/18[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]New York[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] , align: center"]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] "]Approval[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] , align: center"]10/27/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] , align: center"]10/29/18[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]New York[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] , align: center"]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] "]Budget[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] , align: center"]12/1/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] , align: center"]12/14/18[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]New York[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] , align: center"]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] "]Install[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] , align: center"]12/20/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] , align: center"]12/30/18[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]New York[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] , align: center"]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] "]Go Live[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] , align: center"]01/10/19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F1F7ED]#F1F7ED[/URL] , align: center"]01/15/19[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Phoenix[/TD]
[TD="align: center"]C[/TD]

[TD="align: center"]09/10/18[/TD]
[TD="align: center"]10/1/18[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Phoenix[/TD]
[TD="align: center"]C[/TD]

[TD="align: center"]10/17/18[/TD]
[TD="align: center"]10/19/18[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Phoenix[/TD]
[TD="align: center"]C[/TD]

[TD="align: center"]11/11/18[/TD]
[TD="align: center"]12/11/18[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Phoenix[/TD]
[TD="align: center"]C[/TD]

[TD="align: center"]01/3/19[/TD]
[TD="align: center"]02/27/19[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Phoenix[/TD]
[TD="align: center"]C[/TD]

[TD="align: center"]03/3/19[/TD]
[TD="align: center"]03/4/19[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Phoenix[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] "]Survey[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]07/1/17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]08/1/17[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Phoenix[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] "]Approval[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]08/5/17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]09/5/17[/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Phoenix[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] "]Budget[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]02/1/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]02/28/18[/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Phoenix[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] "]Install[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]06/1/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]09/17/18[/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Phoenix[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] "]Go Live[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]09/20/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF8E5]#FFF8E5[/URL] , align: center"]09/22/18[/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]Paris[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]Survey[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]07/1/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]08/11/18[/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]Paris[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]Approval[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]11/5/18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]11/15/18[/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]Paris[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]Budget[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]02/1/19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]09/30/19[/TD]

[TD="align: center"]25[/TD]
[TD="align: center"]Paris[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]Install[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]07/1/19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]08/17/19[/TD]

[TD="align: center"]26[/TD]
[TD="align: center"]Paris[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] "]Go Live[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]08/1/19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F3F3F3]#F3F3F3[/URL] , align: center"]08/31/19[/TD]

</tbody>



Can this be done effectively using standard Excel charting? If so, can someone recommend a chart type?

Or would this better be done using some more advanced Excel functionality?

Looking for advice. Thank you.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Any charting gurus able to point me in the direction of how to best display the overlapping of tasks across Locations and Projects?
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
How do I create a summary report with data from a single tab?
Replies
6
Views
532
AhoyNC
A
M
  • Locked
  • Question Question
Separate and transfer data in different sheet of same workbook based on criteria using macro
Replies
0
Views
467
Muhammad Hussaan
M
F
  • Solved
A simple project update VBA that is not match the projects
Replies
9
Views
417
NoSparks
N
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
242
SBExcel123
S
H
  • Question Question
Excel Table Representation Query: 2 columns in the data set with dates which needs to be aligned to get right result.
Replies
1
Views
575
GraH
GraH

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top