External Links

Quick reference

• 00:04 One of the really common things that users do with workbooks when they are working
• 00:08 with Excel is they actually link data from one workbook to another.
• 00:11 Ad in this video I want to walk through and
• 00:13 show you how this can actually be dangerous practice in a lot of cases.
• 00:18 Where you can, you should always try and keep your data in the same workbook.
• 00:21 Now, you'll notice that I have two files open here.
• 00:24 On the left-hand side, I have a workbook that is called Source Data.
• 00:27 And it happens to hold some information for housing costs that I have here.
• 00:30 So you can assume that this is something that one person works on and
• 00:33 actually keeps this up to date.
• 00:35 Meanwhile on the right-hand side, I have a report file.
• 00:38 So this one I want to actually pull the data from this other workbook in here so
• 00:42 that I can create the rest of the report I am working with.
• 00:45 It's actually easy enough to do.
• 00:47 As long as they're both open, I can actually go and
• 00:49 click in my cell, I can say equals.
• 00:51 And I'll go and click on the cell over here in the other workbook.
• 00:54 And you'll notice it says Source Data.xlsx Sheet1.
• 00:58 And it returns B5.
• 00:59 And when I hit Enter, everything's good.
• 01:02 It pulls across and everything works.
• 01:05 Now, what I want to show you, though, is what happens when things go sideways.
• 01:09 So I'm gonna save this workbook.
• 01:11 This is the report workbook.
• 01:13 And I'm gonna go to File, and we're gonna choose to close it.
• 01:17 And now, we're gonna assume that somebody else has come along and they've taken
• 01:21 my original workbook here and said let's go and insert a new row right here.
• 01:26 And now, we'll save this workbook as well.
• 01:30 I'm now gonna go and I'm gonna reopen the report workbook.
• 01:33 Remember, everything was working just fine.
• 01:36 But now, when I reopen it,
• 01:37 you're gonna see that we actually have a bit of a challenge here.
• 01:40 So if I go and put these together here,
• 01:42 you'll notice that the House Cost is now pointing at B5.
• 01:46 Why isn't it pointing at B6 anymore?
• 01:48 Well, it's because this workbook was closed and
• 01:51 somebody inserted a new row here.
• 01:53 So unfortunately, the Report File doesn't know the changes were made to this
• 01:57 original workbook, so it doesn't update the formula.
• 02:00 Well, the challenge now is that somebody looks and goes this is a mistake,
• 02:03 this row shouldn't be here.
• 02:04 So right-click, and we'll delete it.
• 02:07 But because the two files are open together,
• 02:10 now this file does know that there's been a structural change, so
• 02:14 it updates the formula when you delete that row.
• 02:17 Because it says well, there's a row been deleted.
• 02:19 You want it to point at B5, we got rid of that row, it's now B4.
• 02:21 Isn't that helpful?
• 02:23 So this is a little bit of a challenge.
• 02:25 The only thing that we can do at this point is now re-link this back to
• 02:29 the original set of data in order to make sure that it actually shows up correctly.
• 02:34 And save both files, okay?
• 02:37 So, this is a bit of a challenge that catches people out on a regular basis.
• 02:41 Now, I also want to show you one other thing that can happen where things go
• 02:45 horribly wrong.
• 02:45 So, I'm going to say Close.
• 02:47 And I'm gonna close this guy as well.
• 02:51 Now, at this point, what I want you to recognize is that the file source
• 02:56 data here the report is pointing to is in the same folder level.
• 03:00 What I'm gonna do is I'm gonna move the source data file into this folder
• 03:03 called broken.
• 03:04 We're just gonna get it out of this particular folder here.
• 03:07 So if I look in broken, it's still there, the file exists.
• 03:10 But if I go back to my examples, I only have report.
• 03:13 What I'm gonna do now is I'm gonna open this up.
• 03:16 So open it back up in Excel.
• 03:20 And now, it tells me that automatic update of links has been disabled.
• 03:24 So I'll say that's no problem, I'm going to enable the contents.
• 03:26 And at this point, it comes back and says we can't find some of the links right now.
• 03:30 So would you like to continue without updating?
• 03:32 So in that case, I'd click Continue, and I'd be able to work with the workbook and
• 03:35 it wouldn't update anything.
• 03:37 Or would I like to edit the links that I think are wrong?
• 03:40 So I'm gonna click Edit Links.
• 03:42 And at this point, it pops up this dialogue, and it says hey,
• 03:45 we can't find this file.
• 03:47 So where is it?
• 03:49 Now, we have the option to break the link.
• 03:51 If we were to do that, this would actually break all the links and
• 03:54 just paste them with the values in their workbooks.
• 03:56 So keep the 320, but it would no longer be linked to the original file.
• 04:01 Tther option, we could try to update values, but
• 04:05 this isn't going to go well because it can't find it.
• 04:07 So now, it says well, where is the information you're looking for?
• 04:11 Update values or change source is gonna get you to the same place where
• 04:14 I can now go and say well, this is gonna be in my GoSkills folder.
• 04:18 I'm looking for my External Links.
• 04:20 It's in my Examples, it's in the Broken folder.
• 04:23 There it is, there's Source Data, and now I can say OK.
• 04:26 And at that point, it says hey, everything's good to go.
• 04:29 So we can now say Close, and you'll see that the link is still pointing
• 04:34 to the full file path to where it needs to be.
• 04:37 This is one of the real dangers of working with linked files.
• 04:40 Where new rows get inserted or deleted, or columns get inserted or deleted, or
• 04:44 the file itself actually moves, or is in a place where you don't have access to it.
• 04:49 One of the big things that I recommend if you're going to be linking to external
• 04:52 workbooks, make sure you have a specific sheet in the workbook you are linking to.
• 04:57 And the only purpose of that sheet is to actually reconstitute the cells.
• 05:01 So, basically say on a new worksheet, you could create something if I were gonna
• 05:04 link to this one, I could have a specific sheet that says equals.
• 05:08 And grabs the formula from the other place.
• 05:12 And at this point, I would set up this worksheet specifically for outbound links.
• 05:16 And that way, people aren't gonna change it and aren't gonna mess things up.

Lesson notes are only available for subscribers.