1
00:00:00,570 --> 00:00:07,850
Let's take a look at how you can write formulas that reference cells in other workbooks or other worksheets.

2
00:00:07,890 --> 00:00:13,620
Until now we wrote all our formulas referencing items on the same sheet.

3
00:00:13,620 --> 00:00:20,310
But what if we want to reference cells directly on the other worksheet. For this example let's assume

4
00:00:20,310 --> 00:00:26,000
that our global discount rate is sitting on the referencing sheet and it doesn't have a name.

5
00:00:26,010 --> 00:00:30,930
So we just want to use a direct cell reference to calculate the final price.

6
00:00:30,930 --> 00:00:38,280
So we're going to take price multiplied with one minus the discount rate which is sitting on that sheet.

7
00:00:38,280 --> 00:00:45,150
So to get that cell I can just click on that sheet name, switch to the sheet, and then click on the cell

8
00:00:45,270 --> 00:00:47,550
that I need to include in my formula.

9
00:00:47,970 --> 00:00:49,160
So notice what happens.

10
00:00:49,200 --> 00:00:55,560
Excel automatically puts the sheet name in front of the cell name and it follows it by an exclamation

11
00:00:55,560 --> 00:00:55,980
mark.

12
00:00:56,400 --> 00:00:59,130
That's how Excel recognizes sheet names.

13
00:00:59,160 --> 00:01:05,340
I need to fix this because I'm planning to pull my formula down so I'm going to press F4 and then close

14
00:01:05,340 --> 00:01:07,700
the bracket and press enter

15
00:01:07,750 --> 00:01:12,140
and that's it. I'm going to double click on the side and send the formula down.

16
00:01:12,270 --> 00:01:18,030
If you have a space in the sheet name, Excel also adds this single quotation mark.

17
00:01:18,030 --> 00:01:24,390
If you go to the referencing Tab, I'm just going to add a space somewhere in the middle, click away.

18
00:01:24,570 --> 00:01:29,070
Let's go back to our original formula. Notice what happened.

19
00:01:29,160 --> 00:01:35,300
There is exclamation marks still there but there are single quotation marks surrounding the sheet name.

20
00:01:35,460 --> 00:01:37,410
That's because there's space in there.

21
00:01:37,500 --> 00:01:41,810
If I remove that space, those quotation marks are going to disappear

22
00:01:41,830 --> 00:01:44,820
but all of this is done for you by Excel.

23
00:01:44,820 --> 00:01:49,410
You don't have to worry about putting the exclamation mark or putting those single quotations.

24
00:01:49,410 --> 00:01:54,230
You just have to go to that sheet and click on the cell you want included.

25
00:01:54,240 --> 00:02:00,270
Now what if your discount rate was sitting on another workbook. First thing you need to do is have the

26
00:02:00,270 --> 00:02:02,010
other workbook open.

27
00:02:02,190 --> 00:02:06,920
So let's say that this referencing sheet is actually in its own workbook.

28
00:02:06,930 --> 00:02:13,530
So I'm going to right mouse click, create a copy from it so click move or copy, put a checkmark for create

29
00:02:13,530 --> 00:02:17,580
a copy and let's send this to a new book. And then click on Ok.

30
00:02:17,580 --> 00:02:20,860
So the default name here is Book2.

31
00:02:21,000 --> 00:02:22,680
I'm gonna save this.

32
00:02:22,740 --> 00:02:26,850
Let's go to desktop and save it in a folder called Master.

33
00:02:26,910 --> 00:02:29,890
I'm going to call it Discount Rates.

34
00:02:29,990 --> 00:02:33,550
OK so this workbook is now called Discount Rates.

35
00:02:33,570 --> 00:02:36,440
Now let's switch back to our original workbook.

36
00:02:36,450 --> 00:02:41,970
So using shortcut key control+tab, go to the place we want to write our formulas in.

37
00:02:41,970 --> 00:02:43,450
Let's write them in here.

38
00:02:43,470 --> 00:02:47,540
We're going to do price multiplied with one minus.

39
00:02:47,550 --> 00:02:53,290
Now we need to go to our other workbook control+tab and click on the cell I need.

40
00:02:53,310 --> 00:02:59,320
Now notice again Excel put the entire workbook and worksheet referencing by itself.

41
00:02:59,400 --> 00:03:04,470
And it also fixed that cell reference, close bracket, press enter.

42
00:03:04,680 --> 00:03:05,590
And that's that.

43
00:03:05,590 --> 00:03:07,050
Let's send this down

44
00:03:07,050 --> 00:03:09,230
and these are my discount rates.

45
00:03:09,270 --> 00:03:12,430
So what happens if the other workbook is closed.

46
00:03:12,450 --> 00:03:14,940
Is my formulas still going to run?

47
00:03:14,940 --> 00:03:15,830
Let's test this.

48
00:03:15,840 --> 00:03:20,010
Let's go to the discount rates and close the workbook.

49
00:03:20,010 --> 00:03:27,130
My formula is still there and I see that the address has changed to the exact address of that workbook.

50
00:03:27,130 --> 00:03:29,320
Now let's try one last thing.

51
00:03:29,340 --> 00:03:32,140
Let's change the discount rate in that workbook.

52
00:03:32,140 --> 00:03:33,740
So I'm just gonna open it again.

53
00:03:34,080 --> 00:03:37,670
Let's change this to 50 percent, press enter.

54
00:03:37,990 --> 00:03:43,760
Now immediately if I switch back I see that the discount rate has been applied

55
00:03:43,920 --> 00:03:49,110
and if I close this workbook and save it my discount rate is still there.

56
00:03:49,110 --> 00:03:54,300
So this is how simple it is to reference cells in other workbooks or other worksheets.