Should I link to other workbooks…

…when writing #Excel formula

Note: This post is not about “security” issue 🤷‍♂️

 

Is the following message familiar to you?

Excel Tip - External links

I wonder… when you see this message, which button would you click?  Please let me know by leaving comments below.

From my observation, most people would click “Don’t Update” when they are dealing with a workbook that was prepared by others.

Why is that?

Because most of the time they don’t have access to a “workbook” that is saved in someone else PC.  Even the workbook (the external link) is saved in a shared folder, are you sure your counterparts have access right to that shared folder?

If a user cannot “update” the value the first (few) time they opened the file, he/she would have a tendency to click “Don’t Update” the next time they open the same file…. and worst still, he/she builds a habit of “Don’t Update” every time they see the message, which is super dangerous as he/she may not be working with the updated data that they need.

So, the question is:

Shall I link to external workbooks when writing Excel formula?

My answer is “It depends!”

Depends on what?

On your understanding of “Link to external sources”.

Let’s focus on a super simple example.

You may download the sample files to follow along.

Internal Book

External Book

Take a look at the following screenshot:

Excel Tip - External links1

I have two workbooks opened.  The one on the left is called “Internal Book” while the one on the right is called “External Book”.  In A5 of “Internal Book”, it references to the A2 of “External Book”.  The formula reads

='[External Book.xlsx]Sheet1'!$A$2

which is straight-forward.

And when I change the value in “External Book”, the value in “Internal Book” got updated instantly, which is great!  What’s the problem?

Excel Tip - External links2

Answer:

The value (linked to the external workbook) gots updated instantly is true only when both workbooks are opened.

 

Let’s close the “Internal Book”; and then update the value in A2 of “External Book” to 999.

Excel Tip - External links3

Save the “External Book” and then Close it.

Now open the “External Book”.

This is when you see this message:

Excel Tip - External links4

If I click “Update”… I will get the “updated” value of course!

Excel Tip - External links5

(Note: the formula gives you the full path of the external book when that is closed.)

However, if YOU click “Update”, you will never get an updated value because you won’t be able to access “C:\Users\wongf\…..”, which is my personal notebook. 😜

Excel Tip - External links6

At this point, you have two options:

  1.  Continue without updating the values;
  2.  Edit the link(s)

Option 2 is an option when you somehow obtain the “updated” file from me and save it to a location where you can access to.  When you click “Edit Links”, you may point Excel to the right file by clicking “Change Source…”

Excel Tip - External links7

…followed by browsing the correct file.

(Note: If you have downloaded the sample file “External Book.xlsx”, you can change the source by browsing to the “External Book.xlsx” that is saved in your computer)

If I (or your colleague who sent the file to you) have NOT shared the updated “External Book” to you, Option 2 is not an option at all.

Sadly but true, you can only continue with the current value that was last saved.

By the way, it is not sad if you don’t need the updated value for your work. 😅

Going back to the question:

Shall I link to external workbooks when writing Excel formula?

My answer now is:

It depends on whether you plan to collaborate the file with your colleagues within (or even outside) your organization.  If you do, try to avoid having external links.

If you do not need to collaborate your file with anyone else, it doesn’t really matter as long as you know the consequences and limitations of it.  Having said that, I try to avoid it as long as I can.

Why?

Because I don’t want to see the message after I housekeep my files and folders, occasionally. 😁

Excel Tip - External links6

 

 

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.