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

 

[bschwartz]

If you store all of the data in the same worksheet, with Date being one of your columns, you should be able to create a Pivot Table that you can refresh whenever you add new data, and filter by any week or time period you like.

What version of Excel are you using?

 

[etaf]

you can use
=INDIRECT("'"&$A$2&"'!"&ADDRESS(ROW(),COLUMN()))

in the table

And a data validation with a list of your Sheet names

see here
https://www.dropbox.com/s/r51506y7m3mos9a/Example Workbook_etaf.xlsx?dl=0

 

[Austin9991]

What version of Excel are you using?

I am using 2013.

 

[Austin9991]

Etaf, I won't be able to try that out until later tonight, but it's there a specific place I will need to put the formula? (I can't open your attached file at work)

 

[etaf]

yes in your main sheet
B4 and then copy to the other cells
and the dropdown setup for A2

 

[bschwartz]

I would suggest you try the Pivot Table method. Here's what I did using your example workbook.

On the worksheets 5-30-15, 7-18-15, and 7-4-15 I added a column in column A for Date. Then added the date in each row, example 5-30-15, 7-18-15, etc. I then added all of the rows from 7-18-15 and 7-4-15, excluding the headers, to the bottom of the table in sheet 5-30-15.

Next highlight the entire table on 5-30-15 and go to Insert --> Pivot Table
--> Hit Ok.
--> Go to the Design tab at the top, and change the Report Layout to show in Tabular form.
--> From the field pane on the right, select All the checkboxes except Date
--> From the Options ribbon at the top, select Insert Slicer --> Date
--> You can now choose any date in the slicer and see just the scores for that date.

 

[Austin9991]

OK, I will give that a shot when I get off. You guys are great, hopefully it works

 

[Austin9991]

I would suggest you try the Pivot Table method. Here's what I did using your example workbook.

On the worksheets 5-30-15, 7-18-15, and 7-4-15 I added a column in column A for Date. Then added the date in each row, example 5-30-15, 7-18-15, etc. I then added all of the rows from 7-18-15 and 7-4-15, excluding the headers, to the bottom of the table in sheet 5-30-15.

Next highlight the entire table on 5-30-15 and go to Insert --> Pivot Table
--> Hit Ok.
--> Go to the Design tab at the top, and change the Report Layout to show in Tabular form.
--> From the field pane on the right, select All the checkboxes except Date
--> From the Options ribbon at the top, select Insert Slicer --> Date
--> You can now choose any date in the slicer and see just the scores for that date.

I will definitely try this when I get home. Would it be fairly simple to add new scores as they come out? just add another 5 rows for the employees with the new dates and enter the scores?

 

[bschwartz]

I will definitely try this when I get home. Would it be fairly simple to add new scores as they come out? just add another 5 rows for the employees with the new dates and enter the scores?

What you'll want to do is make the data set into an actual Excel table, for example by selecting a cell in the table, and using the keyboard shortcut Ctrl + T.

This way any new data will be picked up by the PivotTable. Then whenever you add new rows, you'll just have to refresh the PivotTable by selecting a cell in the PivotTable and hitting Refresh on the Options tab at the top.