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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
541
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
 

Forum statistics

Threads
1,089,579
Messages
5,409,119
Members
403,252
Latest member
Keithn1720

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top