Project Overview

[Chris86t]

I have an excel document that details the projects we're working on and who the PM is. plus a lot more detail about that project this is one a tab called Team POAP.

I want to create a summary overview on a different tab (called Timer Tracker) that shows the PM down column A and then in each column going across pulls the name of the project currently working on.

The information I need from the Team POAP is in Column C (Project Name) Column F (PM Name)

I presume creating some VB code would be the best way to do this. However that is beyond my skillset. so I was hoping one of you good folk would be able to help me.

Below is an example of how I would like it to look.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Project Manager[/TD]
[TD]Project Name[/TD]
[TD]Project Name[/TD]
[TD]Project Name[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks
Chris

 

[Toadstool]

Hi Chris86t1,

Is this what you wanted?

Here is the tab with my sample data:

	A	B	C	D	E	F
Stakeholder	Project Manager	Project Name
Big Boss	Bert Bodger	Aardvark P1
Bigger Boss	Sarah Smith	Aardvark P2
Big Boss	Jim Joulier	Aardvark P3
Bigger Boss	Ken Kando	Aardvark P4
Big Boss	Bert Bodger	Aardvark P5
Bigger Boss	Sarah Smith	Aardvark P6
Big Boss	Jim Joulier	Aardvark P7
Bigger Boss	Ken Kando	Aardvark P8
Big Boss	Bert Bodger	Aardvark P9
Big Boss	Sarah Smith	Aardvark P10
Big Boss	Jim Joulier	Aardvark P11

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Status[/TD]

[TD="align: center"]Estimate[/TD]
[TD="align: center"]MY[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]D2[/TD]

[TD="align: center"]$22,000,000[/TD]
[TD="align: center"]87[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]W1[/TD]

[TD="align: center"]$24,200,000[/TD]
[TD="align: center"]68[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]K8[/TD]

[TD="align: center"]$26,620,000[/TD]
[TD="align: center"]98[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]G5[/TD]

[TD="align: center"]$29,282,000[/TD]
[TD="align: center"]66[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]G5[/TD]

[TD="align: center"]$32,210,200[/TD]
[TD="align: center"]82[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]D2[/TD]

[TD="align: center"]$35,431,220[/TD]
[TD="align: center"]84[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]K8[/TD]

[TD="align: center"]$38,974,342[/TD]
[TD="align: center"]94[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]G5[/TD]

[TD="align: center"]$12,000,000[/TD]
[TD="align: center"]92[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]G5[/TD]

[TD="align: center"]$13,200,000[/TD]
[TD="align: center"]65[/TD]

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

[TD="align: center"]D2[/TD]

[TD="align: center"]$14,520,000[/TD]
[TD="align: center"]66[/TD]

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

[TD="align: center"]D1[/TD]

[TD="align: center"]$15,972,000[/TD]
[TD="align: center"]66[/TD]

</tbody>

Team POAP​

Here is the Time Tracker tab and formulae:

	A	B	C	D	E	F
Project Manager	Project Name	Project Name	Project Name	Project Name
Bert Bodger	Aardvark P1	Aardvark P5	Aardvark P9
Sarah Smith	Aardvark P2	Aardvark P6	Aardvark P10
Jim Joulier	Aardvark P3	Aardvark P7	Aardvark P11
Ken Kando	Aardvark P4	Aardvark P8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]No. of Projects[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]2[/TD]

</tbody>

Chris86t1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=IFERROR(INDEX('Team POAP'!$C$2:$C$20, MATCH(0, INDEX(COUNTIF($A$1:A1, 'Team POAP'!$C$2:$C$20), 0, 0), 0))&"", "")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF(A2="","",COUNTIF('Team POAP'!$C:$C,A2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF($A2="","",IF(COLUMN()-COLUMN($B$2)>$B2,"",INDEX('Team POAP'!$F$2:$F$20,AGGREGATE(15,6,ROW('Team POAP'!$C$2:$C$20)-1/('Team POAP'!$C$2:$C$20=$A2),COLUMN()-COLUMN($B$2)))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF($A2="","",IF(COLUMN()-COLUMN($B$2)>$B2,"",INDEX('Team POAP'!$F$2:$F$20,AGGREGATE(15,6,ROW('Team POAP'!$C$2:$C$20)-1/('Team POAP'!$C$2:$C$20=$A2),COLUMN()-COLUMN($B$2)))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IF($A2="","",IF(COLUMN()-COLUMN($B$2)>$B2,"",INDEX('Team POAP'!$F$2:$F$20,AGGREGATE(15,6,ROW('Team POAP'!$C$2:$C$20)-1/('Team POAP'!$C$2:$C$20=$A2),COLUMN()-COLUMN($B$2)))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2
[/TH]
[TD="align: left"]=IF($A2="","",IF(COLUMN()-COLUMN($B$2)>$B2,"",INDEX('Team POAP'!$F$2:$F$20,AGGREGATE(15,6,ROW('Team POAP'!$C$2:$C$20)-1/('Team POAP'!$C$2:$C$20=$A2),COLUMN()-COLUMN($B$2)))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Chris86t]

That appears to be exactly what I want. Thank you very much. I will give it a whirl when I get into work