Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:


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


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.


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
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

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

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 '14 at 12:50
And this is such case right now. Domain does not exist. – s3m3n Mar 29 at 13:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .