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.

Project Manager	Project Name	Project Name	Project Name
A	A	B	C
B	D	E	F
C	G	H	I
D	J	K	L

<tbody>
</tbody>

Thanks
Chris

 

[Toadstool]

Hi Chris86t1,

Is this what you wanted?

Here is the tab with my sample data:

	A	B	C	D	E	F
1	Stakeholder	Status	Project Manager	Estimate	MY	Project Name
2	Big Boss	D2	Bert Bodger	$22,000,000	87	Aardvark P1
3	Bigger Boss	W1	Sarah Smith	$24,200,000	68	Aardvark P2
4	Big Boss	K8	Jim Joulier	$26,620,000	98	Aardvark P3
5	Bigger Boss	G5	Ken Kando	$29,282,000	66	Aardvark P4
6	Big Boss	G5	Bert Bodger	$32,210,200	82	Aardvark P5
7	Bigger Boss	D2	Sarah Smith	$35,431,220	84	Aardvark P6
8	Big Boss	K8	Jim Joulier	$38,974,342	94	Aardvark P7
9	Bigger Boss	G5	Ken Kando	$12,000,000	92	Aardvark P8
10	Big Boss	G5	Bert Bodger	$13,200,000	65	Aardvark P9
11	Big Boss	D2	Sarah Smith	$14,520,000	66	Aardvark P10
12	Big Boss	D1	Jim Joulier	$15,972,000	66	Aardvark P11

<tbody>
</tbody>

Team POAP​

Here is the Time Tracker tab and formulae:

	A	B	C	D	E	F
1	Project Manager	No. of Projects	Project Name	Project Name	Project Name	Project Name
2	Bert Bodger	3	Aardvark P1	Aardvark P5	Aardvark P9
3	Sarah Smith	3	Aardvark P2	Aardvark P6	Aardvark P10
4	Jim Joulier	3	Aardvark P3	Aardvark P7	Aardvark P11
5	Ken Kando	2	Aardvark P4	Aardvark P8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Chris86t1​

Worksheet Formulas Cell Formula A2 =IFERROR(INDEX('Team POAP'!$C$2:$C$20, MATCH(0, INDEX(COUNTIF($A$1:A1, 'Team POAP'!$C$2:$C$20), 0, 0), 0))&"", "") B2 =IF(A2="","",COUNTIF('Team POAP'!$C:$C,A2)) C2 =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))))) D2 =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))))) E2 =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))))) F2 =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))))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Chris86t]

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