Is there an easy way to aggregate data from multiple spreadsheets into one?

[CHBC1]

Hi All,

Once per year my team sends out excel spreadsheets to 22 different departments to get their 4-year staffing plans. The templates we send out have pretty much all been standardized (we had to create one or two specialized spreadsheets, but I'll worry about these exceptions later).

Our goal is to have on spreadsheet that consolidates all 22 departments' staffing sheets. Historically we've had an intern go through and manually link out to each spreadsheet. It's obviously a painfully slow and monotonous process with a chance for error (due to all the manual linking).

Is there some sort of formula or another clever method we can use to import the data we need? Like I said, the staffing sheets are all pretty much standardized, so we want to pull in the same cells from each spreadsheet. The end result would be consolidated file with a large table of data where we can filter by department, year, resource type, etc.

Any ideas?

TIA!

 

[BrianM]

How many sheets are in each department workbook? Are they all named the same? Will all the department workbooks be in the same folder? Is it only one sheet from each workbook you'd like to have merged back into one workbook on one sheet? What is the range of data on the sheet(s) i.e Column A thru M?

Brian

 

[CHBC1]

5-6 worksheets in each department workbook, but I only need data off one sheet. They are all named the same. They are all saved within the same folder.

The tricky part is that we don't want the entire tab...we just need specific cells pulled off of each tab (it's basically a table of data that has staffing info with the four years across the top columns, then the rows have resource type, location, etc).

 

[BrianM]

Oh, and I thought this was going to be easy :D What is the sheet name to be merged from each sheet? Is this sheet in the same position in each workbook? How are the 'specific cells' determined? Are they the same 'specific cells' for each sheet? Can you tell me the range i.e A thru ? and possibly rows i.e 1 thru 10?

Brian