Welcome back to our course on Excel 2016 Advanced.
So far with virtually everything we’ve covered on the course we’ve been looking at self-contained
workbooks, workbooks that have all of their own data within them.
And even when we needed data from outside we imported it into the workbook that we were
using.
But there are occasions when you want to have access to data from outside but on an ongoing
basis.
So rather than import the data into a workbook we want to be able to link to it from one
or more workbooks.
And in some of those instances we may even be depending on what is in effect a real time
update from outside.
Now I’m going to cover three different scenarios here.
In this section I’m going to look at accessing data from another workbook, then we’re going
to look at accessing data from an Access database and then in the third of these sections we’re
going to look at accessing data from the web.
Now as I said, in all three of these cases we’re going to be looking at situations
where the data is held outside of one of our workbooks and in effect we’re not importing
it.
We’re leaving it outside but setting up a mechanism whereby our workbook can get that
data and potentially be updated in real time with that data from that outside source.
Now in the first example that I’m going to use I’ve got some sales data from two
South American divisions of a company.
One division based in Bogota and one based in Lima, Peru.
And for each of them I have their sales figures for 2015.
Now Bogota sales are on one sheet, Lima sales are on another sheet.
And what I’m going to do is put together a summary sheet.
And the summary sheet will combine the two sets of figures.
At the moment all of this data is within a single workbook and we’re going to separate
this into different workbooks a little bit later in this section.
So first of all let me put in the framework for this summary sheet.
Okay my framework is in place.
What I’m now going to do is to copy the figures from the Bogota sheet and the Lima
sheet.
So for the Bogota sheet I’m going to select the cells, copy them to clipboard, back to
the summary sheet, they’re pasted into place.
And then I’m going to the Lima sales, copy those, back to the summary.
But this time instead of paste I’m going to do a special sort of paste, a paste link.
So if I go to Paste Special, go down to the one with the picture of the link on it, Paste
Link.
I see the same numbers of course but now if I click on a sale such as that one, D4, I
don’t get the value 83 in there.
I get a link.
And the form of the link is equals, the name of the worksheet which is Lima, bang, which
separates the name of the worksheet from the cell reference and the cell reference is C4
and that is of course C4 on the Lima worksheet.
Now I should point out that if a sheet name or indeed as you’ll see later on a workbook
name has a space in it then the name of the sheet or the name of the workbook will have
quotes around it in order to identify it as an entity.
But at the moment my worksheet names don’t have spaces in them.
So what I have there is a standard format of link within a workbook.
And then when we separate this out into different workbooks later on you’ll see the more complex
link that arises.
Now as you may not be familiar with the effect of these links let me quickly demonstrate
this.
And the way that I’m going to do this is I’m going to go to the View tab and I’m
going to say New Window.
So we’ve now got two windows.
I’ll increase that one to 200% as well.
And then back to the View tab again and I’m going to say Arrange All and I’m going to
say Vertical.
So I’ve got two windows on the same workbook.
And on the left hand side I’m going to go to the Bogota sheet and I’m going to change
92 to 93.
And just watch what happens in C4 on the right.
That’s on the summary sheet.
And of course what happens is nothing at all.
But if I go to the Lima sheet on the left, go to the first one which currently says 83
and change that to 85 look at what happens to the Lima entry on the right.
Of course that becomes an 85 as well.
So there you’ve seen how updating one cell on a different sheet in a workbook can update
a linked cell on a different worksheet in the same workbook.
Now I should point out while we’re doing this that I’m not really concerned about
the formatting of these cells.
We’re only really concerned with the values and the linkage of those values in this section.
Now I’m going to split this into separate workbooks in just a moment but one or two
other things to quickly show you, which you may not be aware of.
Let’s go to the summary sheet on the right and let me just clear those Lima values.
Now one way that you can set up these pasted links, having cleared the Lima values there
now let me make that same selection from the Lima sheet on the left.
If you hover the cursor over the edge of the selection and don’t left click but right
click and drag, you drag the selection into the right position, that’s in the other
window.
In my case in the right hand window.
When I release the right mouse button I get a little menu here and one of the options
is Link Here.
And that sets up those links.
So that’s a drag and drop way of setting up those links.
And one other little trick that you may not be familiar with.
If I copy the Lima sales figures as before just using the keyboard shortcut Control-C,
go over to the summary sheet and do Control-V to paste them you get a little menu down at
the bottom right there that you may or may not be familiar with.
If you hit the Control key or click on the dropdown that brings up the menu and of course
you can access the Paste Link button from there as well.
So what I’m going to do now is to split this into three workbooks.
The first workbook will contain the Bogota figures, the second one the Lima figures and
the third one will be a summary workbook and that’s going to use the figures from the
other two workbooks.
But it’s going to use them by linkage, not by copy and paste.
Having created those three separate workbooks what I’m now going to do is to open first
the Bogota workbook and the summary workbook and Paste Link the values between the two.
So I have the two workbooks open.
Select the Bogota sales figures and Paste Link them in.
Now notice the reference here from C4 in the right hand window, the Branch sales summary
window.
And you can see that I’ve got the name of the workbook in square brackets, SimonSezIT_South_American_Branch_Sales_Bogota.xlsx,
then the name of the worksheet which is Bogota, then the exclamation mark, what I call a bang,
and then the C4 cell reference.
So with each of these cells I have a reference to a cell in a different workbook.
Of course if I make a change in say the February sales, Bogota sales on the left there and
find that’s wrong and it should be 96 that will be updated in the summary sheet on the
right.
Now what I’m going to do is to close the summary workbook and with the Bogota workbook
here still open I’m going to make another change.
March figure is also wrong.
91 should be 94.
Now notice I’m doing this when the summary workbook is closed.
So tick on that change and now I’m going to close this workbook.
Now what happens next is very important because what I’m going to do is to now open the
summary workbook without the Bogota workbook being already open.
Now the first very noticeable thing there is that you get a security warning which maybe
you weren’t expecting.
And the reason you get a security warning is that as far as Excel 2016 is concerned
you are opening a dangerous workbook because it is a workbook that has a link to the outside
world.
You’re probably familiar with the dangers of opening workbooks which have program code
in them.
But opening workbooks that have links to the outside world is a pretty dangerous exercise
as well.
The good news is that in this case you know it’s not dangerous.
And the other good news is that the way that these security warnings work with Excel as
I’m sure you know is that once you’ve trusted this document then you won’t get
this warning again.
So we’re going to trust this document by saying Enable Content.
Just before I do that look at the March Bogota figure there, 91.
It hasn’t done that update because of course it’s not opening up that other workbook
yet.
Until we trust this one and therefore trust its links we won’t get that update from
91 to 94.
If I now click on Enable Content then the update occurs.
So what I’m going to do now is to close this workbook yet again and then reopen it
yet again.
Now this time instead of the security warning we get a regular warning that you will get.
This workbook contains links to one or more external sources that could be unsafe.
If you trust the links update them to get the latest data.
Otherwise you can keep working with the data you have.
So now you get a choice of updating in which case it will look at the Bogota sales and
see if they need an update or you can say don’t update if you’re happy to work with
what’s there already.
So that’s a pretty straightforward way of giving you the option of updating from those
external links or not.
It does however raise one other issue.
And that is let’s suppose that the whole point of this summary workbook is to perhaps
produce a management report and you want to be able to send this workbook to various people
but they won’t have the individual workbooks, the Bogota workbook and the Lima workbook.
You just want them to see the summary figures basically at a point in time.
But what you can do is to set up this summary workbook so that it doesn’t prompt for those
updates.
Let’s say don’t update for the moment.
Then on the Data tab in the Connections group there is an Edit Links command.
And if I click on Edit Links, notice the links are listed.
Now the links we’ve got here are to the Bogota workbook.
There is a little button in the bottom left hand corner, Startup Prompt.
If I click on that, Let users choose to display this alert or not, one option, Don’t display
the alert and don’t update automatic links, and then Don’t display the alert and update
links.
So if you don’t want the alert and you want the update to happen anyway you could choose
the third option and you won’t get the alert at all.
It’ll just happen.
The option we want though is the middle one.
Don’t display the alert and don’t update automatic links.
Click on OK.
Now I’m going to save this version and of course if I open this version I won’t get
those alerts and I won’t get a security warning.
Just in case you have any doubts about that I suggest that you use the files in the course
files folder, perhaps put in the Lima links as well and just try going through those security
steps that I’ve just described to you to make sure that everything works the way that
I’ve said it would.
So that’s about it on connecting to other workbooks for the moment except for two very
important points.
One of them is that if you look at this particular link here in the formula bar you can see it’s
a very long link, most of which is made up of a folder name.
You can edit and type in link names.
You don’t have to establish links in the way that we’ve said.
Note in this particular case those single quotes though because there are spaces in
folder names in this case it needs those quotes at either end of the name of the workbook
in order to identify it as a single entity.
And the other very important point is that links when created automatically usually contain
folder names or reference to a particular location of a workbook.
And if you move workbooks around that can really mess things up.
So if you are going to use link workbooks it’s important to either, for example, keep
all of them in one folder and remove the folder names, just have the workbook names in those
links so that if you move everything around together they’re still in the same workbook
and therefore don’t need folder names.
Or to keep the source workbooks, in this case for example the Bogota and Lima figures in
a place that never varies.
So you never have to move those workbooks and change links to them.
So that’s something to be very, very careful of.
But that’s it for this section.
We’re next going to take a look at linking to Access data.
That’s the next section.
I’ll see you then.