Thomas,

You can use the indirect function to link to individual files using a
variable to compose the address.

=indirect("'[Budget]"& $a$1 & "'!xxxx"

I generally hard code the links into the files and then find and replace.

This can also be done in a macro using the sheet name or a cell reference it
depends on the requirements.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Thomas" wrote:

Hi community:

I have a "Budget" (excel) file, with one tab/worksheet per location (e.g.
2001, 2002, 2003 etc.).
Let's say I have another set of (Sales) files, one file per location
(2001.xls, 2002.xls, 2003.xls).
In each of these files I want to refer to the respective Budget worksheet,
depending on the store number, the store number being hardcoded in cell A1.
My link in 2001.xls would be "[Budget]2001!xxxx", in 2002.xls it's
"[Budget]2002!xxxxxx" etc.

My question: Is there any way (using Names, Labels etc.) to build an
external link to Budget DEPENDING on the store number?
Reason: I'd like to only change to hardcoded store number (A1) (in 2001.xls
etc.), and then the links would automatically update! Sweet, you must agree
:))

Example:
2001.xls
A1 B1
2001 =[Budget]<WhateverFormulaComesBackWith2001!xxxx

2002.xls
A1 B1
2002 =[Budget]<WhateverFormulaComesBackWith2002!xxxx

Eagerly awaiting your ideas!!!!

Thomas