Locked lesson.
About this lesson
Learn about creating and updating external links, and about the potential dangers of external links in Excel.
Exercise files
Download this lesson’s related exercise files.
External Links - Source.xlsx9.5 KB External Links - Target.xlsx
9.5 KB External Links - Extra Practice.xlsx
13.7 KB
Quick reference
Topic
External links.
Description
Creating, updating and the dangers of external links in Excel.
Where/when to use the technique
When you have little alternative but to link to data in another workbook.
Instructions
Creating
- Open the “Source” workbook and the “Target” workbook
- Go to the Source workbook, and select the cell where you’d like the value from Target workbook to show up
- Type = then, using the mouse, navigate to the Target workbook, click the cell and press Enter
- You can now save and close Target workbook and the link will exist in Source workbook
Updating
- When you open the Source workbook, you should be prompted to update the data
- If you need to update at other times, go to Data --> Edit Links
- Select the links you wish to update and click Update Values
Breaking links
- To convert links to hard coded values, go to Data --> Edit Links
- Select the links you wish to convert and click Break Link
Dangers
- Links are set to certain file paths. If the file you’re linking to moves, then your links will break
- If you insert a new row or column in the target workbook, and the source workbook is not open, the source workbooks formulas will NOT update, and your links will point to the wrong places. (In order to modify the target workbook, the source workbook must also be open to avoid this issue.)
- Your ability to email the file to others is impaired. Unless you send them the target file and they put it in the exact same path on their machine, the links will be broken
- If you decide to make a backup copy of your entire solution, you may find your links pointing to the original data copy unless you are very careful. (The proper method to do this is open both the source and target workbooks, save the target under a new name, then save the source under its new name.
- 00:00 In this video we're going to look at external links.
- 00:07 How to connect to another workbook.
- 00:09 So if we look at the scenario that I have here.
- 00:12 I've got house cost and I may maintain that list of house prices or
- 00:16 apartment cost in an external file, but I want to be able to link this file to it so
- 00:21 that I can just update the price in that other file and everything will work.
- 00:24 We can totally do that.
- 00:25 You just have to be careful of a certain little problem that can potentially
- 00:29 creep in to this.
- 00:30 So lets go take a look at how to set it up in the first place.
- 00:32 What we will do is we will go to file and
- 00:35 I am going to open a recent folder, or file, called target.
- 00:38 The workbook I am in currently is source.
- 00:42 So, Target you can see has house price and apartment price.
- 00:45 So I'm going to go back to my source work book through the Window command here, and
- 00:49 I'm going to say equals and then we'll flip back over to
- 00:54 the Target work book I'm going to click on the $320,000.00 in Sheet1.
- 01:00 So you can see that we get a file name here, the workbook name in square
- 01:05 brackets, Sheet1!, and the cell reference.
- 01:10 When I hit Enter, you'll see it takes me back to the source workbook.
- 01:13 It puts the dollar value in my cell and
- 01:16 it's given me the cell reference that I saw before.
- 01:19 So this is great.
- 01:20 You say perfect, I'm happy with this now.
- 01:22 I'm going to save my workbook, and I'm going to quickly jump back over to
- 01:27 target and I'm going to say file close.
- 01:32 Now what's nice about this?
- 01:34 I can say save, I can file Close.
- 01:38 I can now open up the Target workbook and
- 01:43 I can change the price to $330,000, there we go.
- 01:46 And we'll save it,
- 01:50 File/Close, and we'll jump back over to the original Source workbook.
- 01:57 When we do that it'll pop up this little message that says,
- 01:59 would you like to update your links?
- 02:00 I'll say, absolutely.
- 02:01 And you'll see that my 330,000 shows up there nicely.
- 02:04 And that's kind of cool.
- 02:06 Now, you can also, by the way, update links from the Data tab, say Edit Links.
- 02:11 You can select these ones here.
- 02:12 And you can click Update Now.
- 02:14 And that will actually update the link as well, if you're actually in the file.
- 02:16 You don't have to exit and come back.
- 02:19 Now here's the dangers, though.
- 02:21 This is where things go a little bit sideways.
- 02:22 Let's go File, Close.
- 02:25 And we'll save the workbook now with this 330,000 in there.
- 02:27 We're gonna go back over to the target workbook.
- 02:31 Okay, key piece to remember here is the source right now was closed.
- 02:35 And somebody comes along and says, oh I don't need this row.
- 02:38 And they delete it, and they hit Save.
- 02:43 But I'll Close, and
- 02:45 we go back into the source workbook that was pulling from cell b five.
- 02:51 Would you like to update the links, absolutely.
- 02:55 My price changed, 175, why?
- 02:58 This file is still pointing at B5, and notice what happens when the other file
- 03:02 is closed, is we get the long full length to the file path here.
- 03:06 The problem is, is that this file isn't aware there was a change
- 03:09 made to the structure of the other file.
- 03:11 Because it wasn't open at the same time.
- 03:12 And you think, well, that's no big deal.
- 03:14 Let's just open up the Target workbook here.
- 03:19 And we'll put that line back in, Insert.
- 03:24 We'll go over to Window again.
- 03:26 To Source, it's still pointing at 175, because now both workbooks are open,
- 03:31 the link's been shortened to the workbook name, and now it is aware that
- 03:35 you're doing something, because it knows that both the workbooks are open, okay?
- 03:38 So this is what's going on here.
- 03:39 It's a little bit weird.
- 03:40 So let's say, you know what?
- 03:41 The heck with this.
- 03:42 Let's close this one.
- 03:44 And we won't save.
- 03:46 We're going to save file close the target workbook as well and we won't save it.
- 03:51 Now to be save here we'll reopen the target.
- 03:55 We'll put this row back in.
- 03:57 We'll save and close.
- 04:02 Actually we don't even need to close.
- 04:03 We'll just leave that one open now.
- 04:04 We've saved it.
- 04:06 Now when we go back and
- 04:07 we open the source workbook it will again be pointed at the correct cell.
- 04:13 At this point, if I were to flip over to the target workbook,
- 04:19 and I were to delete this row, because both workbooks are open
- 04:23 this one would move up and now In the source,
- 04:27 it's still pointed to the right place because the formula's changed.
- 04:29 So this is one of those things to be aware of when you're working with
- 04:32 linked workbooks, is if you're making modifications,
- 04:36 you should have both of them open at the same time.
- 04:39 One last thing you need to be aware of when working with external links
- 04:42 is how to break them.
- 04:43 Because the problem is if I sent you this workbook today,
- 04:46 it would be hard-coded to the file path on my system, so it wouldn't update for you.
- 04:50 It's just going to return you an error.
- 04:52 To clear that out what we do, is we actually select the link
- 04:54 of even the entire worksheet if we want to get all of them.
- 04:57 And we go to edit links We find the links we wanna deal with, and
- 05:01 we click break link.
- 05:03 It'll give us a confirmation.
- 05:05 We can say break link, that's fine, and close.
- 05:08 And you'll notice that this has now been converted into the hard-coded number, so
- 05:12 when I send it to a user somewhere else, they'll be able to see this, even though
- 05:16 they won't be able to update it, through the file path to the original workbook.
Lesson notes are only available for subscribers.