Formula to retrieve data for multiple worksheets from one master data sheet

[John Freeman]

I have two excel workbooks. One workbook has just one worksheet with all the data I need. I have another workbook with at least 35 worksheets that I need to populate. Let say each worksheet represents a state. I want to track the average temperature, income, population by month. Month is listed by rows and the avg temp, income and population is listed by column. What formula can I use to pull the specific data from the data worksheet to populate or pick up the values for each respective state's avg temperate for the month of march? I have attached a sample. Assume the last worksheet call datasheet in a separate workbook. I have attached it all in one for sampling purposes.

Please help.

Thanks in advance.

[RichardWinter]

John,

See the attached book for the solution.
In each of your reporting sheets you need a cell where the name of actual the sheet is stored (A3 in the example) .
To get the sheet name I found a neat formula at ozgrid.com
http://www.ozgrid.com/VBA/return-sheet-name.htm
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) returns the name of your sheet.
To get your values you can use my favorite sumproduct function:
=SUMPRODUCT(--(INDEX(Datasheet;;1)=$A$3);--(INDEX(Datasheet;;2)=$A5);INDEX(Datasheet;;COLUMN()+1))
I have named the range in your datasheet. I prefer named ranges as formulas are easier to read and with the F3 Key you can get your selection when typing along.
The sumproduct looks for your sheet name (asumed that this is identical with the labels in your datasheet) and the category and adds up the value column (here defined by COLUMN()+1) as the columns in the datasheet have an offset of 1).

I guess there are more solutions but this is the one I use all the time -

Have fun
Richard

PS: The datasheet doesn't need to be in the same book. Range names can refer to ranges in other workbooks.