formula with variable parameter

[GM]

I am doing a comparison of financial results. I have five
columns which represent quarters. The financial results
for each quarter are stored in different xls files, so I
am bringing information from these files into my
comparison file.

I want to create a formula for each cell on the first
column of my comparison sheet that is generic enough that
would allow me to copy it to the correspoding row in the
other four columns. The problem is that each column makes
reference to a different file, so if I do a copy, the file
path remains static. I want to make use of a parameter to
allow the formula to look in the proper file, in the
proper directory, based on the heading of my column.

What I have:
A1:Q1
B1:Q2
A2:=+'S:\FinRep\Q1\[B100]Sheet1!B2
B2:=+'S:\FinRep\Q2\[B100]Sheet1!B2

what I want:
A1:Q1
B1:Q2
A2:=+'S:\FinRep\{xyz}\[B100]Sheet1!B2
B2:=+'S:\FinRep\{xyy}\[B100]Sheet1!B2

Where {xyz} makes reference to A1 and gets replace by Q1
and {xyy} makes reference to B1 and is replaced by Q2.

I've tried multiple thinks, but haven't found a way to
evaluate text in a cell as a formula and produce a value.

Any light you might shed would be greatly appreciated.

 

[Harlan Grove]

I am doing a comparison of financial results. I have five
columns which represent quarters. The financial results
for each quarter are stored in different xls files, so I
am bringing information from these files into my
comparison file. ...
what I want:
A1:Q1
B1:Q2
A2:=+'S:\FinRep\{xyz}\[B100]Sheet1!B2
B2:=+'S:\FinRep\{xyy}\[B100]Sheet1!B2

Where {xyz} makes reference to A1 and gets replace by Q1
and {xyy} makes reference to B1 and is replaced by Q2.

I've tried multiple thinks, but haven't found a way to
evaluate text in a cell as a formula and produce a value.

If these files were open you could use INDIRECT. If they're not open, you'll
need to use one of the alternatives mentioned in the following archived article.

http://www.google.com/[email protected]