External Links

Quick reference

• 00:04 In this video, we're going to look at a common solution to a challenge and
• 00:10 the dangers that are associated with it.
• 00:13 So here's the challenge.
• 00:15 Right off the bat, I've got a file over here that is collecting some
• 00:19 statistics and my intention is to give this to someone else to maintain but
• 00:23 I have a separate workbook for reporting.
• 00:26 I don't want the person that's capturing the data to have access to all this,
• 00:29 they don't need it.
• 00:30 And I don't want to have all of their data in my file because I want to be able to
• 00:34 maintain it individually.
• 00:36 Now this on its own isn't a big issue.
• 00:38 Just as if I were going to link to an internal worksheet, I could go and
• 00:41 say equals or my house cost here.
• 00:43 Make sure the other workbook is open, select the cell and
• 00:46 you'll notice that I get an absolute cell reference.
• 00:49 Between the square braces, we've got the name of the external workbook and
• 00:53 we've got the name of the worksheet that this cell references on.
• 00:57 And when I hit enter, you can see that it comes up with $320,000.
• 01:01 So this is pretty darn cool.
• 01:03 Now, what happens if somebody opens up the file and they change the value in here?
• 01:10 So we'll go to $350,000.
• 01:13 Everything works nicely.
• 01:15 What happens if they then go and say, hey, you know what we're going to do?
• 01:19 We're going to actually move this stuff over here because we
• 01:21 want to format it a little bit differently.
• 01:23 Does everything still work?
• 01:26 And what you can see is where we had originally linked to B5,
• 01:29 this is now C5, and the formula has flowed through properly.
• 01:33 So no big deal, right?
• 01:36 Well, here's the thing.
• 01:37 I'm now going to go and save my source data file.
• 01:42 And I'm going to save my report.
• 01:44 And now, what I'm going to do is I'm going to close my report file.
• 01:49 And at this point, I'm telling the person that's maintaining this, hey,
• 01:52 don't worry about it, you can do anything you need to do, it's all good, and
• 01:56 they go and make a change.
• 01:57 They come back and they say, you know what what,
• 01:59 I actually really want to see just indented just another level like this.
• 02:02 And then we're going to go and we're going to make some changes to this and
• 02:05 bring it down a little bit and there we go, okay, cool, now it looks much better.
• 02:09 I'm happy with the way that actually looks and we'll save the file.
• 02:14 So what's going to happen when I now go and reopen my report file?
• 02:20 Let's go take a look.
• 02:22 And you'll notice that my house cost is no longer pointed to the $350,000.
• 02:26 It now says, house cost, and
• 02:27 that's giving me a value area because I can't multiply house cost by 30% markup.
• 02:32 What is going on?
• 02:33 Well, the problem is this is still pointed at cell C5.
• 02:38 The user moved the data around.
• 02:40 And yet we saw that happened before and it was fine.
• 02:43 But it was only fine when the reporting workbook with
• 02:46 the link was open at the same time as the original source data file.
• 02:51 If this one's closed, and we make modifications here inserting or
• 02:55 deleting rows or columns or moving fields around, those changes in references do not
• 03:00 get pushed through and updated into this workbook, it blows things up.
• 03:04 And you think, well, that's not really a big deal,
• 03:06 right, because this one's supposed to be in C5.
• 03:08 So what we'll do is we'll just go and
• 03:10 move these things back to where they need to be.
• 03:13 Notice that it's still pointing to house cost.
• 03:16 Where's it going?
• 03:17 Let's update the formula here, it's pointing to B5.
• 03:20 Because this workbook is open,
• 03:22 those changes are still actually flowing through.
• 03:25 So I'm going to come back over here, press Ctrl+Z, put this back where it was.
• 03:29 The only really real recourse I have here is now is to relink the formula.
• 03:33 So this is a real scary thing because you don't know how to predict this, right?
• 03:37 It all depends on what users do.
• 03:40 Now, let me try something else here.
• 03:42 I want to show you another thing.
• 03:43 We're going to save this.
• 03:45 We're going to save this one.
• 03:46 And what we're going to do is we're going to close both files.
• 03:52 Right now source data is actually located inside the same folder as report.
• 03:56 We know what the file path is.
• 03:58 So what I'm going to do is I'm going to go and
• 04:01 move this into a new folder called Broken, all right?
• 04:05 Let's see what happens when we actually go and
• 04:07 open the report, because it's no longer at the same location.
• 04:11 And what you'll notice, it comes back and it says, hey,
• 04:15 we can't find some of the content.
• 04:17 What would you like to do?
• 04:19 You can continue without updating their values or
• 04:21 edit the links you think are wrong.
• 04:22 Now, if you continue, it won't do anything to the links.
• 04:24 You can work with things.
• 04:25 It just won't change any values.
• 04:27 But I want to be able to fix things so I'm going to click Edit Links.
• 04:32 And inside here you'll notice it says, hey, I'm sorry but the source file is not
• 04:36 found, I'm looking for this particular location, so what do we want to do?
• 04:40 And we have a couple of different options that we can actually play with here.
• 04:43 The first one is to break the link, what this will do is it will convert any
• 04:47 of these linked values into hard coded value.
• 04:50 So instead of having a link here, it would just convert it to $350,000 and
• 04:54 make it a static value.
• 04:55 That's the easiest way to break your links and
• 04:58 actually make sure that you're not linking to anything external.
• 05:01 The other option is that you can go to change source and try and
• 05:05 find the file to repair the links.
• 05:08 Now I'm going to go and
• 05:08 actually browse into the folder where I actually have my source data file.
• 05:12 Of course, you'd need to navigate to wherever you
• 05:15 actually want it stored in and we'd say, okay.
• 05:17 And that will update the links so they're pointing to the new target and
• 05:21 at this point everything will link correctly.
• 05:23 Now the reality is if your file is stored inside a SharePoint or OneDrive sync
• 05:27 folder, and you're moving it around, these links will probably update for you.
• 05:32 On the other hand if you're sending a file by email or
• 05:35 you're actually working with a non syncing folder,
• 05:38 it is very likely that you will end up in the situation where links are broken and
• 05:42 need to either be broken so that they're hard coded values or relinked.
• 05:46 One word of advice that I would give you is if you are going to link to
• 05:49 external cells,
• 05:50 I would never link to cells that are in the middle of a report format like this.
• 05:54 I would actually go and
• 05:55 create a separate workbook over here that had something like house cost.
• 05:59 Links back to the original page.
• 06:04 So this is a specific purpose driven worksheet for linking.
• 06:07 I would actually link to that and I would probably even go and
• 06:10 hide the worksheet so that nobody can modify it.
• 06:13 And that way nobody's going to be inserting new columns or rows and
• 06:17 breaking my links in future.

Lesson notes are only available for subscribers.