Level 2 - Lesson 23 - External References
Category: [General,Excel] Demo Available
External References
The term external reference with regards to Excel can be one of the following methods
Formulas
Charts
Drawing object
Pivot Tables
Named ranges
Microsoft Query
Hyperlink
We will look at the two most common methods Formulas and Named ranges.
Formulas
When we create a spreadsheet it is at times necessary to retrieve some of our data from another Workbook. The most common method to do this is with external formulas. These are simply formulas that refer to a range on another Worksheet in another Workbook. When we create a formula that refers to another Workbook we have Linked the two Workbooks. The reasons for doing this can vary greatly, but possibly the two most common reasons are to break a large Workbook into smaller Workbooks and thus reducing the size of the Workbooks. Another reason could be that the writer of the spreadsheet does not want the user to be able to view or access the raw data very easily.
Once we have written a formula that refers to another Workbook we have linked the two Workbooks together. The Workbook that houses the formula(s) is known as the Linked Workbook and the Workbook that the formula(s) refers to is known as the Source Workbook.
Before creating any External references, consider the possibility of placing a copy of the data you have in the Source Workbook onto a Worksheet in the Linked Workbook and hiding the Worksheet, eg; Format>Sheet>Hide. This can save having to create a Link and also having your data spread over different Workbooks. Of course this really only applies to static data.
When we link a formula to another Workbook and the Source Workbook is open, we need only include the Workbooks name, Worksheet name and range address. See below:
=SUM([Book2]Sheet1!$A$1:$A$10)
When we link a formula to another Workbook and the Source Workbook is not open we need to include the full path as well as the Workbooks name, Worksheet name and range address. See below:
=SUM('C:\My Documents\Doodlings\[Book2.xls]Sheet1'!$A$1:$A$10)
There are three ways the above two formulas can be written.
Type them into a cell manually (not recommended)
Open the source Workbook and write the formula using the mouse pointer and Window to the Workbook(s)
Write the formula in the source Workbook then cut and paste it into the linked Workbook (recommended)
We personally much prefer the third method especially if the formula is a nested one. This way you do not have to keep switching between Workbooks via the Window. The second method is fine for a simple formula like in the above example. In our opinion, the first method is asking for problems.
When we use either method 1 or 2, Excel will only place in the Workbook name and the Worksheet name. The File path will be added automatically as soon as the Source Workbook is closed.
If the Source Workbook is saved as another name while the Linked Workbook is open, Excel will change the formula in the Linked Workbook to reflect the new name. If however the Source Workbook is saved as another name while the Linked Workbook is not open, the formula in the Linked Workbook will continue to refer to the old name. If we moved the Source Workbook from within Windows Explorer, our formula in the Linked Workbook would continue to refer to the old location whether is was open or not!
At times any of the above can cause unwanted results. This is especially true when we open a Linked Workbook, say YES to update links and Excel tells us it cannot find the Source Workbook! It then displays the Link not found dialog box. This means you must locate the Workbook you wish to Link to. Once you have located it and clicked OK, Excel will change all the formulas for you. Even though this happens, it pays to avoid this if possible. We can do this by making use of the Links dialog box as described below.
Updating External Formulas
Whenever you open a Workbook that contains links to other Workbooks, Excel will display an error message telling you your links have been disabled. Select the Options button and you will be presented with two options. If you select Enable this content, you will update all linked formulas in the Workbook.
The links in a Workbook can also be updated when the linked Workbook is open by going to the Edit Links button under Connections options on the Data tab. If the Links option is greyed out then the Workbook contains no Links. The Links dialog box will contain a list of all external links for the Workbook. At the bottom of the Links dialog is the Update: Automatic and Manual Option buttons. If your Workbook does not contain linked Objects such as Charts, pictures etc the Manual option will be greyed out.
If the Linked Workbook has not been updated the Update Now button will be enabled. If you press this button Excel will update links for the selected Workbooks in the Link dialog box. To select more than one Workbook hold down your Ctrl key when selecting. The Open Source button will open the selected Workbook(s). The Change Source will change the name and/or path of the selected link. When you click this the Change Links dialog box will appear. Locate the file and/or path you wish to change to and click OK. This will then change the link for all formulas that were using the Workbook you selected to change.
If you only wish to change the linked formulas of a chosen range of cells select the cells and then use the Replace option under the Find & Select button located under Editing options on the Home tab to change the name and/or path of the selected cells.
Named Ranges
As mentioned it is also possible to create a link to another Workbook using a Named range that refers to another Workbook. When we do this we create a Link to an External reference. The same principles that apply to external formulas also apply to external named ranges. By this we mean that other than the method used create the Named range, all we have mentioned above applies to Named ranges that refer to another Workbook.
The method to create an external named range is as below:
Open the Workbook you want to refer to
Click back to the Workbook you want to create the name in
Go to Define Name under Defined Names options on the Formulas tab (Ctrl+F3
Type a name in the "Names:" box
Make sure the "Refers to" box is empty
Click the collapse dialog button on the right of the "Refers to" box
Select the Workbook to link to (should be able to see it on your task bar at the bottom of the screen, or by going to View>Window options>Switch Windows)
Highlight the range and click the expand dialog button
Click Add then OK.
You can now use the named range in a formula as you would normally. We cannot use the Cut and Paste method for external Named ranges.
Any named ranges that refer to an external Workbook will not show up in the Name box (to the left of the Formula bar). To see the range you must go back to the Name Manager (Ctrl+F3).
That is the fundamentals of external references in reference to Formulas and Named ranges. Normally Excel has no problems keeping track of external references, but at times it gets itself mixed up. When this occurs you end up with what is known as Phantom links. When this happens Excel still thinks it has a link to a particular Workbook when in reality it doesn't and each time you open the Workbook Excel tells you it cannot find the Workbook and presents us with the Link not found dialog. When this happens a bit of trickery may be called for.
Go to the Name Manager and make sure you do not have any named ranges referring to an outside Workbook.
Go to Data>Connections>Edit Links and try to use the Change Source button to refer your link back to the your open workbook. In other words try and change the link so it refers to itself.
Open a new workbook and create a link to it and Save. Now go to Edit Links and use the Change Source to refer the link to the new Workbook. Save again and then delete the link you created and again Save.
There is also a Break Link button in the Edit Links dialog. If you hit this button, Excel will permanently convert your formulas and external references to their existing values. It is a good idea to save a copy of your file before you do this as once you have done it, you cannot undo the action.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets and Index to Excel VBA Level 1 Free Lessons and
Index to Excel Level 2 Lessons and Index to COVID-19 Charting examples