External Links

Quick reference

• 00:04 In this video we're going to look at linking from one workbook to another and this is something that is actually quite common in practice but it's
• 00:10 one of these techniques that you should avoid wherever possible and you'll see why as we go through this video what the dangers are.
• 00:17 So just to keep in mind when you're building a model if you ever can, try and keep everything within the same workbook.
• 00:23 Link from one worksheet to another, no problem but try and keep it in that same workbook.
• 00:28 Reaching out to other workbooks has challenges as we'll see right here.
• 00:31 You notice we've got two different workbooks setup. We've got Master and we've got our Source workbook they're both contained in the
• 00:37 same directory right now, as you can see from my folder structure here.
• 00:41 And what I have is in my Source workbook I've got the cost of building a house, in my Master
• 00:45 workbook the cost of the house that I'm going to pull from my source workbook,
• 00:50 as well as a markup in my sales price.
• 00:53 So to create the link is very very easy. It actually works the same as
• 00:56 when you go and create a link to a worksheet. We click in the cell that we want.
• 01:00 type in equals
• 01:01 and we go and click in the other workbook cell B7.
• 01:06 And you'll notice though one difference here is that this comes in as
• 01:09 absolute when you're linking to another workbook. It doesn't really make a difference in this
• 01:13 case unless you're trying to copy that formula and grab a range of cells from the other workbook. So we can leave this as B7
• 01:19 and hit Enter and you'll see that my link comes through with the correct dollar value. Perfect.
• 01:27 Now I can save the workbook,
• 01:31 and when I close the workbook
• 01:35 and I'm going to close my source workbook as well.
• 01:37 File Close
• 01:39 Reopen the master workbook again.
• 01:43 You'll see you we get a nice little warning about our links may be unsafe. We can just say OK update those
• 01:51 and we now know that we're actually pointing back to the correct cell which is great.
• 01:55 So if I go and open the source again,
• 02:00 we can see that this is all good.
• 02:03 Now if I were to go and change the price, lets drop that down to 180,000
• 02:07 and we jump back to our other workbook we can see that it's already actually put that change in place.
• 02:13 This is where workbooks work nicely when they're actually linked together.
• 02:16 Here's where the dangers come in. Let's just save the master. Then we'll close it.
• 02:22 Now the master is closed.
• 02:26 If I go and insert a new row in my source workbook
• 02:30 and I save it,
• 02:32 and now I go back to open up my master workbook again.
• 02:37 I'll just bring him back on screen.
• 02:41 And what happened? It's not pulling from the total anymore why is that?
• 02:45 Well the challenge is that this is still pointing at cell B7 which is where we set up our original formula.
• 02:50 Because the master workbook was closed when I went and inserted a new row here, Excel doesn't know or at least the master
• 02:57 workbook didn't realize that there was a shift in cells so it never updated the formula.
• 03:02 But what's interesting here is that if we go back and we were to actually delete a row,
• 03:07 you'll see that it will actually update the formula now. If I go back its now looking at cell B6 which is still the $80,000.
• 03:16 So we'd have to go back and reset this formula obviously if we wanted it to work correctly. That's one of the dangers that happens when you're
• 03:22 working with linked workbooks that are actually going between two different areas and you
• 03:26 start modifying the source file when the master files not open
• 03:30 is that the links start to break and you may not notice that they actually have different numbers in them. It's kind of a
• 03:35 scary thing. So if you're using workbook or linked workbooks you
• 03:40 always have to open everything before you start making changes to the structure, inserting or deleting rows or else you'll end up compromising
• 03:47 the integrity of your model. So that's one danger point that we have to be aware of.
• 03:52 Now I'm going to save this and I'm going to close this one again. We'll close the master
• 03:58 and I'm going to save my source file as well
• 04:01 and I'm going to close it too.
• 04:05 Now what I'm going to do is I'm going to take my source file and I'm going to move it into a different directory.
• 04:12 So the master is still right where it was. I'm going to go back and open up the master
• 04:20 and it says "hey I can't find or the links might be unsafe. What do you want to do?" I'm going to update them.
• 04:25 And it says "hey, you can't!" What's going on now?
• 04:29 So this is a bit of a problem. If we hit Continue
• 04:33 nothing really happens for us right now. It's trying to link to a file that doesn't exist which isn't going to help and if I hit
• 04:39 Enter on it, it's going to ask me if I want to find it. But you may not know where that
• 04:42 thing has actually gone and that's the real challenge in these things.
• 04:45 So you've got to be careful when you're moving different files around as well, some other
• 04:50 problems can come up. Now we can fix these if we go in to Formulas
• 04:54 and I'm just going to open this up a little bit actually so we can see the full menus that we need here.
• 05:00 My mistake, it's on the Data tab under Edit Links.
• 05:04 I can actually go and click on this and say
• 05:07 change source and then I can go back and actually find my directory where the file is. It's in C:\GoSkills\Broken
• 05:17 There's source and say OK and at this point it will actually go and say alright now I can find this
• 05:21 and I can link it again. So that's better, that's how we actually go about fixing them as well. But
• 05:26 sometimes you'll end up getting a whole bunch of #N/A values that come back as well because it can't find them, or a #REF! or something.
• 05:32 But definitely a problematic indicator at any rate. So things to be really careful about as I say
• 05:38 you won't have these issues if you try and keep your entire model contained within the same workbook. It's when you start reaching out to
• 05:44 other workbooks that rows being inserted or deleted, columns being inserted or deleted, can mess up the original master if its not open.
• 05:51 Files moving around can also cause some major problems. So definitely some things to be aware of when you start going to play in that arena.

Lesson notes are only available for subscribers.