Drop down list question

[Austin9991]

Hello all, longtime lurker, first time poster here.

Background:

My work uses a survey-based system to "grade" our store on its customer service so to say. There are 5 metrics that we are graded on, and they calculate the scores based on the averages derived from our weekly surveys. I have been able to break down the scores they give into individual numerical grades for each employee in our store in order to get a better grasp on who is underperforming or exceeding expectations. Currently I just have a 2-sheet workbook that I update weekly with the new scores that are posted. One sheet breaks down how many surveys each employee was present for, and how many perfect scores they received out of their total number. The other sheet pulls that information and turns it into an "out of 100" score and puts it in a tidy chart. Eventually I would like to be able to run queries or reports showing monthly averages for each employee, however that is a problem I will solve in the future.

Problem:

I'd like a much easier way to organize these without saving a new workbook each week with the corresponding week ending date. I've been trying to figure out if I can put a drop down list in the title of the chart, where you would select a week ending date and it would auto-fill the relevant scores into the main chart. That way I can just select a date from the list and view the chart very quickly. I'm assuming the first step would be combining all the charts into one workbook, and just hiding the sheets. However I am not sure what sort of formula I would need to use in the dropdown list in order to reference the hidden sheets and auto-fill as needed.

Any tips or solutions you all can come up with? Here is a link to a sample workbook (I quickly merged a couple charts together in separate sheets for a working, visual reference): https://jumpshare.com/v/fG7xNmOGI4iyzyjUyQqK?b=Y62i5KYfWidCrfEkDE22

Also, here is a jpg showing what I am trying to get to auto-fill after selecting a date from the drop down list: https://jumpshare.com/v/8TEtLPKUpowdS2qrI3Ko?b=Y62i5KYfWidCrfEkDE22

Thank you so much for any help,
Austin

 

[Austin9991]

Thank you to everyone here for your help. I tried both methods (Formula and Pivot Table). Both have worked exactly how I wanted them to, however the Pivot Table is just so clean and professional looking so I must go with that as the solution. If I have any trouble adding new fields to either method listed above, I will update this thread.

You all are awesome!

Austin