Created by Shane Devenshire and David Hager
The problem – to 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.
Unfortunately, this type of formula will only work if the referenced
workbook is open. Shane came up with part of the solution to this
problem when he discovered that the INDEX function can return a linked
cell value from a hard-coded link range. For example, if you define a
range as “ref1?, where the linked range formula is:
(A smaller range starting at A1 can also by used.)
then you can use the formula:
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:
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. However, there is a workaround for
this limitation, and the formula in the 1st argument is part of that
process. It converts the value in Sheet2!$A$1 into a number between 1 and
29. Then, if you define the preceding formula as oref1 (and other similar
formulas as oref(n)), you can use the following master formula:
Now, if you give this formula a defined name (say mref), then the resulting
“omnireference” can be used in place of ref1 in Shane’s formula to produce
an “omnilink” that is capable of returning values from 29 x 29 (841)
different links. This formula is the one that is finally entered in a
worksheet cell.
Note: This technique works great as long as the linked files are not moved,
renamed or deleted.
Editor’s Note: I like to add a little commentary to these Experts E-Letter posts or an alternative example. I ran through the example given (short of setting up 58 named ranges) but I’m struggling to think of a practical example for this. I’m sure someone else can think of one, so leave a comment. Thanks.
I have a related problem but with DDE links. I link Excel to a real-time stock quote provider, with a formula such as:
=bc|cot!GOOG.ult
(where GOOG is the stock symbol)
I would like to have a cell where I could change the symbol, without changing the formula.
Any ideas?
Thanks!
We put together a daily summary of about 20 different pages from various spreadsheets from various departments and send it off as a management report.
I have a master sheet with a list of spreadsheet, server locations, pages, ranges and orientation, with a macro that loops through each copying each set to a new daily master.
At the moment they must be all open.
Not anymore…
Hui…
Regarding =IF(ISERR(INDEX(ref1,ROW(),COLUMN())),””,INDEX(ref1,ROW(),COLUMN())) formula:
You can achieve the same without using INDEX and complex formulas. Define name B1S1 to be =[Book1]Sheet1!A1 and put =B1S1 in any cell to get a cell with same location from Book1’s Sheet1.
Exactly!
The only major drawback I have encountered with this technique is the breaking of the hard-coded formula links by changing filenames, folder locations, server names,…etc.
Regards,
David Hager