…when writing #Excel formula
Note: This post is not about “security” issue 🤷♂️
Is the following message familiar to you?
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.
Take a look at the following screenshot:
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?
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.
Save the “External Book” and then Close it.
Now open the “External Book”.
This is when you see this message:
If I click “Update”… I will get the “updated” value of course!
(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. 😜
At this point, you have two options:
- Continue without updating the values;
- 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…”
…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. 😁
wmfexcel 