"Flexible" External Links

[Thomas]

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

 

[Martin Fishlock]

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.

 

[Conan Kelly]

Thomas,

Martin Fishlock is correct, you can use the INDIRECT function to do this.
You might also be able to use the ADDRESS function.

But what I've found out is that when you use the INDIRECT function, you need
to have the source file open in order to get your formulas to
update/recalculate. So in that regard, it is kinda pain in the butt.

As for the ADDRESS function, I'm not sure if it is this way or not.

As Martin mentioned, if you want this link to an external file w/o having to
have the file open, you will need to hard code it. And he also said that
you could create a macro that will do a find an replace based on what you
enter in a cell.

HTH,

Conan

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.

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