OfficeTips Home  VBA Section  General Section  Download Section  Privacy Policy 
Change A Link In A Formula Without Changing The Formula 
This can be done with the INDIRECT
function by creating a concatenated string with input from several
worksheet cells which contain workbook (in A1) and worksheet (in B1)
names.
=INDIRECT("'["&A1&"]"&B1&"'!A1")
Unfortunately, this type of formula
will only work if the referenced workbook is OPEN. Now
the INDEX function can return a linked cell value from a hardcoded link
range. For example, if you define a range as "Reference1",
where the linked range formula is:
=[Book1.xls]Sheet1!$1:$65536
then you can use the formula:
=IF(ISERR(INDEX(Reference1,ROW(),COLUMN())),"",INDEX(Reference1,ROW(),COLUMN()))
in any cell and the returned value
will be from the same cell in Book1.xls on Sheet1. Then, variable links
to this formula can be made by changing the link range as referred
to in a named formula. This formula is of the form:
=CHOOSE(Sheet2!$A$129*INT((Sheet2!$A$11)/29),Reference1,Reference2,...,Reference29)
where Sheet2!$A$1 is an input cell
for values from 1 to n which represent a particular link stored as
a defined name. As you are probably aware, the CHOOSE can only accept 29
arguments. This technique can be extended to handle up to 841
(29x29) links.
Note: This technique works great as
long as the linked files are not moved, renamed or deleted.

Copyright 19992014 (c) Shyam Pillai. All rights reserved.