variable path in a link

[Guest]

Is there a way to use a variable in the path of a link. I would like to be
able to define the variable by typing it is a specific cell then update all
my links accordingly

For example say I have workbooks named pOffice22, pOffice23 etc in a
folder. All of these files are the same except they contain data from
different weeks. I have a summary workbook that I use to perform various
reports on this data. The data is transferred into the summary workbook via
links.

I would like to be able to type 22 in a cell and get the data from file
pOffice 22 or type 23 in the cell and get the data from file pOffice23.

Any help will be greatly appreciated
Christy

 

[Gary Brown]

Take a look at the InDirect function in HELP

 

[Guest]

Thanks Gary but I don't see how that helps me.

I have a workbook that will get data from several different (closed)
workbooks via links. I want the paths in those links to be variable and be
able to define that variable in a cell.

If the source folder contains workbooks named; xName1, yName1, zName1,
xName2, yName2 and zName2 - I want to type 2 in a predetermined cell and
link to the data in xName2, yName2 and zName2.

 

[Tim Williams]

=INDIRECT("[Week" & A1 & "]Sheet1!$A$1")

Where (eg) A1 has your week number

Tim

 

[Bob Phillips]

INDIRECT will not work on closed workbooks. For that you need Harlan's Pull
routine http://tinyurl.com/5zsxx

--

HTH

RP
(remove nothere from the email address if mailing direct)

=INDIRECT("[Week" & A1 & "]Sheet1!$A$1")

Where (eg) A1 has your week number

Tim

Thanks Gary but I don't see how that helps me.

I have a workbook that will get data from several different (closed)
workbooks via links. I want the paths in those links to be variable
and be
able to define that variable in a cell.

If the source folder contains workbooks named; xName1, yName1,
zName1,
xName2, yName2 and zName2 - I want to type 2 in a predetermined
cell and
link to the data in xName2, yName2 and zName2.