Get ALL External Links with

THIS Simple Excel Trick

(as NEVER seen before!)

A terrific feature of Excel is the ability to pull data from external sources, like other Excel files.

But with any benefit, there comes a cost.  In this case, keeping track of the links and where the links point can be difficult to remember.

The Way Most People Check Links

When opening a file with external links, we are presented with a banner notification stating that the file contains links to external data, and do we wish to allow those links to pull the content.

We can click the Enable Content button, allowing this to take place.

To check the links, where they point and their status, we can select Data (tab) -> Queries & Connections (group) -> Edit Links.

In the Edit Links dialog box, we see a list of the links with the following information:

  • The source file that the link points towards
  • The type of link (workbook or worksheet)
  • How the link updates (automatically or manually)
  • The status of the link (“Source is Open”, “OK”, “Warning”, “Unknown”, etc.)

Although the Source column displays the name of the linked file, you must click each link, one at a time, to see its location.

Generating a List of External Links

What if you could type a command like the following and generate a list of the links:

=GetLink

Using the Dynamic Arrays Calculation Engine, we are presented a horizontal list (spill range) of all external references.

Because this is difficult to read, we can orient the list vertically by wrapping the formula in a TRANSPOSE function.

=TRANSPOSE(GetLink)

This “trick” is brought to us by veteran contributor Bob Ulmas (Excel MVP – 25 years).

Bob combined the new, soon to be released Dynamic Arrays with the old Excel 4.0 Macros.

These “hacks” come from page 92 of Bob’s book, “This isn’t Excel, it’s Magic!”.

Working with Excel 4.0 Macros

Excel 4.0 was equipped with a library of fully-functional macros.  Although these macros were introduced in 1992 (yes; 27 years ago), they remain present in all the Excel versions that followed.

Let’s look at two practical examples of combining Dynamic Arrays with Excel 4.0 Macros:

  • Create a list of all external links in a file
  • Create a list of all files in a specific folder

List All External Links

If we look at the following file, we see an example of a cell with a link to a cell in a different Excel file.

To utilize one of the built-in Excel 4.0 macros, we must create a name in the Name Manager that references the macro with a user-friendly name.

To generate a list of all external links in a file, perform the following steps:

  1. Select Formulas (tab) -> Defined Names (group) -> Name Manager

  1. In the Name Manager dialog box, click “New…

  1. In the New Name dialog box, we will name the reference “GetLinks” and reference the LINKS macro by entering “=LINKS()” in the “Refers to” field.

  1. Click OK to close the Name Manager dialog box.

To create the list of external links, select a cell and enter the following formula:

=GetLinks

NOTE: For the above to work, you must be using the new calculation engine of Excel

If you don’t have the new engine, you can use this formula instead: =INDEX(GetLinks,ROW(A1)) and then drag down. To avoid the #REF error, wrap it inside the IFERROR function.

As we witnessed earlier, to make the list easier to read, we can wrap the above formula inside the TRANSPOSE function to create a vertically-oriented list.

=TRANSPOSE(GetLinks)

This allows us to more easily keep track of our links and whether they are pointing to the correct workbooks.

Updating the Links

What happens if we add, update, or remove a link from the macro-generated list?

Because these are macros, they do not execute (update) unless requested.

To update the list of external links by initiating a full sheet recalculation, press the key sequence…

Generating a List of Files

in a Defined Folder

To generate a list of all files in a defined folder, perform the following steps:

  1. Create a folder reference in a cell, such as the following in cell B3 on a sheet named Sheet1:
C:\Users\LG\Desktop\Confidential

  1. Select Formulas (tab) -> Defined Names (group) -> Name Manager

  1. In the Name Manager dialog box, click “New…

  1. In the New Name dialog box, we will name the reference “GetFiles” and reference the FILES macro by entering “=FILES(Sheet1!$B$3)” in the “Refers to” field.

  1. Click OK to close the Name Manager dialog box.

If we select a cell and type the formula…

=GetFiles

…it fails with a #N/A error message.

The reason this fails is that we need to supply the FILES macro with search parameters.

If we wish to list all files in a folder, we can add a “slash-asterisk” to the end of the path pointer in cell B3.

C:\Users\LG\Desktop\Confidential\*

The asterisk is a wildcard that denotes “any file”.

To get the list to read vertically, we wrap the formula in a TRANSPOSE function.

=TRANSPOSE(GetFiles)

NOTE: Remember to press CTRL-ALT-F9 to update the list.

If we want to see the results in sorted order, wrap the formula in a SORT function.

=SORT(TRANSPOSE(GetFiles))

If we wish to only see Excel workbooks, we can update the search parameter to “\*xlsx”.

C:\Users\LG\Desktop\Confidential\*xlsx

A Reminder About Saving Files

Because these files are using macros, you must remember to save the files with a “.XLSM” file extension for Macro Enabled Workbooks.

Excel NEW Dynamic Arrays Course 

BE THE FIRST to master Dynamic Arrays

From Beginner to Expert Level!

Start Learning

4 Comments

  1. Manish October 25, 2019 at 8:26 am - Reply

    Hi
    The given trick is very nice

    Thanks…

  2. Mark October 31, 2019 at 12:54 am - Reply

    It was only a matter of time until somebody found a way to use Dynamic Arrays in a way that I’m sure Microsoft had not intended. I love the mix of new and old.

    I’m sure there must be some good tricks with UDF’s and Dynamic Arrays waiting to be found.

  3. Dean Holloway November 12, 2019 at 11:13 am - Reply

    Hi, this looks like a neat trick but I cant get the ‘GetLink’ command to work in Excel? it just returns #NAME? – is there another setting i need to have switched on?

    Thanks for your help.

    • Leila Gharani November 14, 2019 at 9:01 am - Reply

      Make sure in the name manager you have LINKS() as the original formula.

Leave A Comment

Share This