Manipulate a set of spreadsheets - Create a roll up master spreadsheet with aggregate data

Background: We have approximately 60 separate excel files that contain approximately 50 tabs each. Each tab contains the same columns but different row data. Some files contain similar row data but not identical. The data relates to retail locations and the price of a product on a given date. Each tab on the file is a different state within the USA. The Desire/Intended Outcome: • Have a place/tab that calculates the past trend data for each row, on each tab, to allow us to see the median and mean price that was offered for this product, at each ‘location’ over the history of our data. Have the same data calculate the median and mean pricing over the most recent 12 weeks of data, the most recent 26 weeks of data, and the most recent 52 weeks of data. • Add data each week when new pricing comes out, and have that data ‘recalculate’ the mean and median pricing trend as listed above. Some simple method to paste or merge files to allow the new data to be imported into the master spreadsheet that contains the calculations and graphs. • Have a line graph that shows the past 12 months of pricing data for any location, by clicking that location from a drop down to allow for the line graph to be displayed, and then printed. • A version that allows us to print on one page the 12,26, and 52 week data trends for any one location. All of this should be done in Excel, not in another program. We should be able to drop in/paste in the latest weeks data to allow for the data to update A sample version of one of the 60 spreadsheet files is attached. Please see all of the tabs on the sample file.