Using dynamic links to external workbooks in Excel

Question

I have a master workbook that needs to pull data from a specific dated subfolder. The date is specified by the user prior to macros being run.

The master workbook acts only as a presentation layer, and the source/underlying data in the dated subfolders have consistent names and formats.

Assume that within my master workbook on 'Sheet1' I have the following function in A1:

='C:\Development\GridsResults\20101115\[DATA_sheet_20101115_D.xlsx]Stresses'!$C$9

You will notice that are two 'dated' parts in the file link - this needs to be supplied by the user. In this case, the date supplied is 15-Nov-2010. Now if the user supplies 23-Nov-2010, the above link should be

='C:\Development\GridsResults\20101120\[DATA_sheet_20101120_D.xlsx]Stresses'!$C$9

I already have a formulae that create the above file paths, within my Links sheet in my master workbook. This is the dynamic part which creates the links. Now in the Links sheet, assume that result of my magic resides in cell B3 and is a named Range called DATA i.e.

C:\Development\GridsResults\20101120\[DATA_sheet_20101120_D.xlsx]

So in theory, what the outcome should be, is that cell A1 on Sheet1 in my master workbook should use this generated file path

="'" & DATA & "Stresses'!$C$9

Is there a combination of standard excel functions that can achieve this?

Solution

Use =INDIRECT("'" & DATA & "Stresses'!$C$9").

Comments

[stray.leone]

doesn't the referenced workbook need to be opened to use this?

[Toc]

yes it need to be opened

[Andrea]

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes or disappears.

[s3m3n]

And this is such case right now. Domain does not exist.