Change A Link In A Formula Without Changing The Formula

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 hard-coded 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$1-29*INT((Sheet2!$A$1-1)/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.