Transcripts for the above video clip:

EXTERNAL LINKS PROJECT

In this section you’ll learn how to identify external links in a spreadsheet and what they actually are.
Change the Source of these external links to another spreadsheet, and where necessary Break the Links totally from these other spreadsheets.

Links between workbooks is a very common feature in the business world. The ability to link one workbook to another adds a lot of value to developments; however it adds a lot of risk as well.
And Microsoft seems to have accepted this and they have actually built in a tool to address this very risk, however, not many people know about this tool and how it can be used. So for example here we have two versions of input data,

• let’s say this is Version 1 where costs were set as this level,
• and then a second version will setup level 2. This is the later version and this should be the most recently used.
• If you open up another workbook, and this pops up so it warns you that there are some sort of external links and it just wants to know should it update or not update, from that you know there are external links,
• you can click Update, and it might tell you its having problems to update,
• the only way to address that is to look at this feature built into Excel. In this version of Excel you can go directly to it, other versions you have to use the method well use now,
• so I’m going to say Continue,
• so here’s the spreadsheet, its got a number of Products, the Quantity required, then there’s a Cost per unit, and that goes to the relevant spreadsheet.
• in this case it goes to Input Version 2 and has a look at it, but because there are external links, it is highly recommended you go use the Links feature,
• so if you click on Edit,
• there’ll be a button called Links, if you click Links
• what it brings up is all the links that this spreadsheet links to outside of its own spreadsheet, so what you’ll see is
• it tells you is that there is one source called a sub Couloring Cells Exercise,
• there seems to be some cells linking to Input Data Version 1, which obviously should raise alarm bells,
• and there is some information linking to Input Version 2 which appears to be correct.
• Now you have a number of other buttons available here, if you click Update Values, it goes and fetches the relevant information from the correct spreadsheets,
• there is the ability to change the source, what this allows you to do is you can click on a source and in this case Input Data Version 1 is a concern, so we would click on that
• and we’d say look we want to Change the Source, it’ll then say well where do you want to change too, and you can surf to find the correct spreadsheet,
• and in this case it’d be we want it to go back to Input Data version 2, we’ll click on it,
• when you say ok,
• what happens is all the cells in this spreadsheet that used to refer to Version 1, are automatically updated to refer to Version 2,
• what is important to realize when you do something like that when you change a source, is that the structure of the spreadsheets must be exactly the same, because all Excel is doing is replacing where it looks. It doesn’t try and assess whether rows have been inserted, so its very important you understand that

Other buttons, you can Open the Source, so

• by clicking on a button
• you can click Open Source, and it will open the relevant spreadsheet,

You can as well Break a Link, and the way you would do this is you specify that a particular cell should not be linked anymore, you can break it. Now in this spreadsheet here, this Colouring Cell Exercise appears to be an error, and what we can do is

• we can click on it
• and we say Break Link,
• when that happens Excel will warn you, that when you break a link its permanently gone, it give you the option to pull out of it,
• once you say Break Links, the link is broken and that cell is no longer linked.

However, the Links feature in Excel does not help you identify where these external links are, and sometimes when you open a spreadsheet and you discover there are links, you just want to find out where they are to understand what is being used from other workbooks. In order to do this, you should use another one of Excel’s inbuilt features, which is the ‘Find Replace” command, which is explained later