Page 22 - Finanancial Management_2022

Define Name Range name with external links

However, if the model doesn’t contain links, I would receive
a prima facie error, as shown in the screenshot “Range Name
This opens the box shown in the screenshot “New Name With No External Links Error”.
Dialog”.
Range name with no external links error
New Name dialog

In the first instance, Office 365 has spilled the references;
ie, it has listed the references in adjacent cells along the same
row. Good old Excel: It does like to default to the incorrect
choice. To counter this great default “feature”, if I were to use
TRANSPOSE, suddenly things become much more readable,
as shown in the screenshot “Range Name Transposed”.
Range name transposed

Here, I have created a new range name, called listlinks,
which refers to the formula:
=LINKS()

If I were to type this formula straight into a cell, I would get
the following (aforementioned) error shown in the screenshot
“Invalid Function Error Screen”. Voilà! All your links are presented dynamically.
This is just so much simpler than convoluted VBA code or
Invalid function error screen using third-party software. All you need is the ability to spill
your results; ie, your version of Excel supports dynamic arrays
(presently, this means using an Office 365 version of Excel).
That’s all there is to it.
Word to the wise
Excel 4.0 functions stored in defined names may only be saved
in macro-enabled workbooks (.xlsm or .xlsb). If you are using
this feature in conjunction with dynamic arrays, the file will
have to be generated using Excel 365, too, so do be aware of
these limitations when incorporating this functionality into
existing workbooks.
It should also be recognised that the VBA approach will
identify cell locations, whereas the new trick detailed above
But if instead I were to type in =listlinks (ie, my freshly does not. However, links may occur for other reasons that the
minted range name), I wouldn’t get an error if the model macro may not locate. Therefore, it’s a case of using the right
contains links to external data sources (as shown in the approach for the right scenario. ■
screenshot “Range Name With External Links”) and you have
an Office 365 version of Excel.

Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a Modelling. Send ideas for future Excel-related articles to him
global consultancy specialising in Excel training. He is also an at liam.bastick@sumproduct.com. To comment on this article
Excel MVP (as appointed by Microsoft) and author of Intro- or to suggest an idea for another article, contact Jeff Drew at
duction to Financial Modelling and Continuing Financial Jeff.Drew@aicpa-cima.com.

20 I FM MAGAZINE I February 2022