Thread: Extracting data from another spreadsheet

I have two spreadsheets, one with raw data and the other a summary file. I'd like to use formulas in the summary file to automatically extract the relevant data from the raw file. The raw file is something that comes anew every week, with a different file name, so I'd have to be able to update the file name each week. The raw file also has multiple worksheets within it, so I'd need to refer to a specific worksheet. How do I go about doing that?

My thinking is that I'd like to have a cell in the summary spreadsheet wherein I can enter the full file name of the raw spreadsheet (e.g., "C:\raw.xls"), another cell wherein I can enter the worksheet name ("Worksheet 2"), and then have multiple references throughout the summary file that make use of both the file name and worksheet name to create absolute references within the raw file. Is this possible? If so, how?

Spreadsheet 1: raw-013012.xls (raw data added for Jan 30)
Worksheets: Jan 1; Jan 15; Jan 30

Spreadsheet 2: summary-013012.xls (summary file to prepare incorporating Jan 30 data)

In raw.xls, I have data for # of sales in the three different periods, each with its own worksheet. I get a new raw file every 2 weeks, and the date at the end of the filename changes each time.

I'd like to be able to take the data from the cell in each separate worksheet and import it into the summary spreadsheet. So, for example, the sales data always appears in cell D5 of the raw worksheets. That means I can retain the same cell reference in the summary file, but the raw spreadsheet filename and worksheet name change every time. My thinking, then, is to just have a cell in the summary file wherein I can enter the names of the correct raw spreadsheet and worksheet, which will then update all of the references in the summary file to refer to the correct raw spreadsheet/worksheet names.

Each of the references in the summary file should look as follows:

='C:\sales reports\raw-013012.xls]Report January 30, 2012'!$D$5

But I should be able to alter the filename and worksheet name above simply by updating the cells in the summary file, such that if I change the filename to "C:\sales reports\raw-021512", all of the references in the summary automatically update to reflect the new filename.

How do I do that?