How to do a top 10 that updates automatically when the data is updated?

Hello,

I am trying to do the following.

On one sheet, I have A to AA columns that are capturing the data that I need.

I would like to do the following on separate sheet if possible.

On the sheet Project-Portfolio:

Capture:

Column D7-D312 Project Type (Funded Project, Un-Funded Project, Pilot Program, Production Program (Four categories))
Column E7-E312 Project Name (various)
Column AA7-AA312 Project Score

Project Type - Project Name - Project Score (Highest to lowest top 10)
Funded Project David 400
Funded Project David 2 355
Funded Project David 3 354
Etc

Second set same headers:
Un-Funded Project John 1 322
Un-Funded Project Jonh 2 320
Etc


Is this possible to do within Excel? I am assuming it is, but if you have looked at some of my older posts, I am still pretty new at this.

Thanks

You get better result on your question if you add a small excel file, without confidential information.

Please also add the expected results manualy in your file.

1) Where do I find the data in the file?

2) Where do I find the expected result in the file?

Although you tried to inform us, please also add the expected result in your file.

Mark the related cells with the same backgroundcolor.

Ok, in the attached spreadsheet. You will find four tabs across the bottom. Project-Portfolio, Top Ten, Totals, Dropdown

On the first tab, Project-Portfolio, the only columns I want to zero in on are: D, E, and AA so those three columns will have data in them. Only three pieces of data, but they have data in those columns.

On the Top Ten tab, you will see the following:

Project Type (which correlates to column "D" from the Project-Portfolio Tab)
Project Name (which correlates to column "E" from the Project-Portfolio Tab)
Score (which correlates to column "AA" from the Project-Portfolio Tab)

I want to see the top 10 or in this case the top three based on score, and separated by Project Type. So if the project is labeled as "Funded Project" I want to see the top 10 for Funded Projects based on the score from column "AA".

If the project is labeled "Un-Funded" I want to see the top 10 Un-Funded Projects based on the score from column "AA". Of course this will be on the top 10 tab, but in a different section. Once I get the first section figured out, I can do hopefully apply that to the additional reporting section I need, so I am not to worried about the second part.

with a pivot table.

I first made sure there is text in the headers, so I added a - in the cell P4.

After that I made a pivot table.

After that I sorted them from high to low.

You can also use a top 10 (or top 7), but this is to get you started.

See the attached file.

If its unlikely you'll have two projects scored the same then you could do this without a pivot this way

In B6 of top ten tab put this formula

=IF(D6="","",INDEX('Project-Portfolio'!$D$7:$D$311,MATCH(D6,'Project-Portfolio'!$AA$7:$AA311,0)))

In C6

=IF(D6="","",INDEX('Project-Portfolio'!$E$7:$E$311,MATCH(D6,'Project-Portfolio'!$AA$7:$AA311,0)))

and in E6

=IFERROR(LARGE('Project-Portfolio'!$AA$7:$AA$311,ROWS('Top Ten'!$D$6:D6)),"")

Now you've only put 'funded projects' in your list. I notice you want to separate two lists into funded and non funded. That will mean more formula work too so Pivot table might be your easiest option