I have a workbook that has many sheets. Most of the sheets contain raw data. One sheet referred to as "Quick Stats" contains a bunch of various tables that take the raw data and give it meaning, such as: how many tests were taken in January; which teacher gave out the most tests; etc.). I'd really like to be able to display the stats for any given period without having to create additional sheets or tables. I'd like to select a period and have all the formulas on my stats sheet display values from the sheet that contains the data for that period.
Is there a way to have a cell (maybe a cell with a drop down list?) whose content becomes the reference point in the formulas?
For instance my sheets are named AY 13-14 (aka. academic year 2013-2014). So if I have a formula like below
=SUM('AY 13-14'!C:C)
how can I make the sheet name of 'AY 13-14'! be the result of the value of a cell and have the formula reference the correct sheet?
Is this even possible?
Your formula will have to use INDIRECT reference based on the value of the cell with the drop down list.
For example: if you have your drop down list in the cell QuickStats!A1, then your formula will be
=SUM(INDIRECT(QuickStats!A1 & "!C:C"))
(Your drop down list would have to contain names of data sheets, e.g. AY 13-14 etc)