Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 0 down vote accepted

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

share|improve this answer
    
doesn't the referenced workbook need to be opened to use this? –  Ahmad Nov 29 '10 at 8:11
    
yes it need to be opened –  Toc Nov 30 '10 at 2:01
add comment

I do not believe this would be possible using the standard Excel functions. if it is it would definitely not look pretty.

I think your best bet would be to create a macro; the activation method would be up to you, of course. The macro would look something like this.

Dim L1 As Variant, L2 As Variant
L1 = ThisWorkbook.Names("DATA").RefersToRange
L2 = "='" & L1 & "Stresses'!R9C3"
Sheets("Sheet1").Range("A1").FormulaR1C1 = L2
share|improve this answer
add comment

http://numbermonger.com/2012/02/11/excel-pull-function-creating-dynamic-links-to-closed-workbooks/

The Link above vcould be a good option. Didn't had the time to test it myself.

share|improve this answer
    
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. –  Andrea Mar 28 at 12:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.