External Links

Quick reference

• 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.