Pivot Table Help Needed: How to create PT from data validation list

I need to create a pivot table from a changing cell value.

Cell A1 has a data validation list (drop down list). Dependent on what is selected, C1 value is displayed.

In A5:A7, I need to list top 3 agents and their scores. I've tried to do this using large() and index() & match().
Problem, if any of the scores are the same, that highest agent is repeated.

Since the data are in tables, I thought of using pivot tables. But, I don't know which table (at report run time) which table would be used.

Another wall, the boss wants to keep the workbook as *.xlsx (free of code)...grr

I've searched high and low, but, without manually creating the PT every week or using vba (there's that wall), I'm at a loss.

Any help?

Hi,

Are you able to attach the xlsx file instead of a docx file?

peterrc

Unfortunately, no......as mentioned in the docx file, it has proprietary info/data on it and I can't send it outside of the company. I'm sorry. I know it's better to have the xlsx file.