Welcome to Highline Excel 2013 class video number seven.
If you want to download this workbook, click on the link below the video.
In this video we want to talk about three different types of references or cell references.
We want to talk about sheet reference, workbook reference and a 3-D reference.
Now, here's the first one, I'm going to talk about sheet reference.
We have a "Schedule of Accounts Receivable" and here's the balance, the "AR", accounts receivable balance.
Over on the "Controlling Account" sheet, we also have a balance, and in accounting, you need to check whether these are in balance.
So, over here, that means I need to have this cell, on sheet "CR(8)", in one formula and I need the cell A5 from the "CAR" sheet.
No problem, you ready?
"Equals", and then I'm going to click on this B11, that's just on this sheet,
are you equal to, now, here's the sheet reference, the first thing is, you click on the sheet,
then you click on the cell in the sheet, you can see up here "CAR!"
That's a sheet reference, it knows that's the sheet name, "CAR",
the exclamation point is the syntax for sheet reference and then A5.
Now you hit enter.
What you don't want to do is click back on the sheet.
Enter.
And so F2, we can see our formula there.
Now, let me just show you something.
We don't have any spaces in this name, so there's no little single apostrophes.
If I were to change, just like anything in Excel, if I were to change the sheet name, then of course the formula will change.
F2.
Sheet reference have to have single apostrophes if there's a space.
I'm going to come back over here and get rid of it.
F2.
There we go.
So, sheet references.
Now let's go look at "CR(9)" and we'll talk about a workbook reference.
Now here, we have a formula for "Current Net Income",
F2
This is for June.
Now, I'm going to assume that the May net income is in a different workbook,
and what we're going to do, so you can do this while you're watching the video,
is we're going to cut this, CTRL+X,
I'm going to open up a brand new workbook, with the keyboard CTRL+N,
CTRL+V,
I'm going to CTRL+ roll, just to make it a little bigger,
name this sheet,
F12 to "Save as", you can save it wherever you want,
OOPS! I did the cardinal sin,
I left it Book1,
F12,
I'm going to call it "MayNetIncome",
Now this workbook name will show up in our formula, so that's very important and helps us identify it.
Alright, now you ready?
The keyboard to jump back and forth between windows is ALT+Tab,
so I'm holding ALT and hitting the tab key,
so I could use this, but there's a keyboard just to jump back and forth between the open excel workbooks, its CTRL+Tab,
So, CTRL+Tab.
Alright, you ready?
So, "May Net Income",
Equals, CTRL+Tab,
and now I'm going to click on "Revenue".
Notice, right up here, it puts the actual name of the workbook file in square brackets,
When we close this, it will actually put the whole file name in,
and then it says "May", exclamation point cuz it's got a sheet reference, and then absolute reference,
now, it, by default, comes up as absolute.
It's easy enough to change that, you just put your cursor up there and change it, but that will be fine.
And then I'm going to type Minus, SUM, open parenthesis, and this whole range right here, closed parenthesis.
Those are two workbook references, so now when I hit enter,
F2,
you can see we have some workbook references.
Now another way to do that, is if, there I calculated it, it might make more sense, CTRL+Tab,
to actually calculate the net income over here, right?
Then we only have to have a single----
I'm going to CTRL+Up arrow, Minus, SUM, tab, Up arrow, hold Shift,
up, up, up, up, up, with my up arrow, Closed parenthesis, CTRL+Enter,
so F2, that is our formula,
CTRL+Tab, it might make more sense to do the formula over there and then say Equals, CTRL+Tab,
and click on that.
So now we have a workbook reference, sheet reference and an absolute cell reference.
Again if we had to change this, like copy this down,
F2,
you just take your cursor and touch it and then hit F4, F4 and make it whatever you want.
That's fine for us, cuz we're not copying it anywhere.
Now the change, Equals, the current month, Minus, the previous month and we get the change.
Now, CTRL+Tab, CTRL+S, and let's close this.
Now watch what happens right here,
F2, so it puts the whole file name, that's how it knows how to communicate.
Alright now let's close this,
open it from wherever you have it, and if this were to change,
CTRL+S, ALT+F4,
now when we open up our workbook with our workbook references,
it's going to ask, "This workbook contains links to one more external sources and could be unsafe."
I'm going to click update, 'cause I know that it's safe and instantly it updates.
Now if you ever move that workbook, it may not be able to communicate, you can re-establish a link by going up to "Data"
"Edit links",
and then you can "Update", that's if you initially clicked "don't update",
"Change Source", "Open Source", "Break Link" sometimes you no longer want---
your number is done, you don't want that dialogue box to pop up anymore when you open your workbook.
"Change Source" here, that means you go and re-find it and double click on the workbook and re-establish.
Alright, Close.
So, workbook links.
Now let's go over and look at our third type of reference,
and I'm going to click the "Corporate" sheet and we're going to talk about 3-D references.
I'm going to use my navigation arrows to expose more sheets, 'cause here's the deal,
we have a template, products January through April,
and we have three stores, Oakland, Seattle, Tacoma,
and we want to, on the "Corp" sheet, add up all of the PRO1's for January, from across three sheets,
Now let's go look at this sheet, "Oakland".
Well January "PRO1" is in B3,
same on Seattle, B3 and same on Tacoma.
So, this 3-D cell reference works when the layout on all the different sheets are exactly the same.
Now, notice, what is it that we want?
We want the B3 from across three sheets,
that's why it's called 3-D, because we're going to get the same cell, B3, B3, B3,
as if they are one on top of each other from across three different sheets.
And you can have as many sheets as you want. Alright, ready?
We're going to add those, so I'm going to go "Sum",
then I come over, and step one is I click on the sheet, so far it's looking like a sheet reference,
I click on B3 on the first sheet,
notice, looking just like a sheet reference, but watch this,
before I click on the last sheet, Tacoma, hold Shift.
When I click on Tacoma, instantly,
this is highlighted and look what happened up here.
We have Oakland, colon, Tacoma, B3, and that colon is awesome,
cuz just like a regular range of cells, if I were to insert a sheet between the two bookends,
Oakland and Tacoma, this formula would totally update.
Alright, that's a relative cell reference, I'm going to close parenthesis,
CTRL+Enter,
F2, just to admire that handiwork.
Now I can copy this down, and over.
So, go to the last one, F2.
Now it's looking at all of the E8's from across Oakland all the way to Tacoma sheet.
If you were to insert a sheet,
so I'm going to Shift+F11, and then let's say I copied this, just to show you that it works.
Let me delete everything, I'm going to go over to the Corporate Total, ok, so January is 31,
if, on this sheet, this is a new store, this is Burien,
just to give us a big number so we can immediately see, 5000,
instantly over here it updates.
The formula doesn't look like it changed but just like regular ranges of cell,
we have Bookend, Colon, Bookend, so anything inserted in between updates.
Now here we did a cell reference, we could also do,
if we want to do a range of cells from across some sheets, we can do that.
So, I go Equals, SUM, now I'm going to click,
I just want January, I don't want the individual products,
so I'm going to click on the first sheet, highlight the whole range,
you can see up here it says Oakland B3 to B8, that's a range, now hold shift before you click on Tacoma,
and instantly we see our formula.
closed parenthesis, CTRL+Enter,
and so now I can copy this over, click in the last one, F2.
Absolutely amazing!
So, that's a 3-D cell reference, we also saw a sheet reference and a workbook reference.
Alright, next video we'll talk about table references in formula table nomenclature.
Alright, see you next video.