Project Overview

Chris86t

Board Regular
Joined
Feb 27, 2013
Messages
79
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 ManagerProject NameProject NameProject Name
AABC
BDEF
CGHI
DJKL

<tbody>
</tbody>

Thanks
Chris
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
618
Office Version
2016
Platform
Windows
Hi Chris86t1,

Is this what you wanted?

Here is the tab with my sample data:

ABCDEF
1StakeholderStatusProject ManagerEstimateMYProject Name
2Big BossD2Bert Bodger$22,000,00087Aardvark P1
3Bigger BossW1Sarah Smith$24,200,00068Aardvark P2
4Big BossK8Jim Joulier$26,620,00098Aardvark P3
5Bigger BossG5Ken Kando$29,282,00066Aardvark P4
6Big BossG5Bert Bodger$32,210,20082Aardvark P5
7Bigger BossD2Sarah Smith$35,431,22084Aardvark P6
8Big BossK8Jim Joulier$38,974,34294Aardvark P7
9Bigger BossG5Ken Kando$12,000,00092Aardvark P8
10Big BossG5Bert Bodger$13,200,00065Aardvark P9
11Big BossD2Sarah Smith$14,520,00066Aardvark P10
12Big BossD1Jim Joulier$15,972,00066Aardvark P11

<tbody>
</tbody>
Team POAP

Here is the Time Tracker tab and formulae:

ABCDEF
1Project ManagerNo. of ProjectsProject NameProject NameProject NameProject Name
2Bert Bodger3Aardvark P1Aardvark P5Aardvark P9
3Sarah Smith3Aardvark P2Aardvark P6Aardvark P10
4Jim Joulier3Aardvark P3Aardvark P7Aardvark P11
5Ken Kando2Aardvark P4Aardvark P8

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

Worksheet Formulas
CellFormula
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

Board Regular
Joined
Feb 27, 2013
Messages
79
That appears to be exactly what I want. Thank you very much. I will give it a whirl when I get into work
 

Watch MrExcel Video

Forum statistics

Threads
1,095,783
Messages
5,446,474
Members
405,403
Latest member
horace james

This Week's Hot Topics

Top