Let's take a look at how you can write formulas that reference cells in other workbooks or other worksheets.
Until now we wrote all our formulas referencing items on the same sheet.
But what if we want to reference cells directly on the other worksheet. For this example let's assume
that our global discount rate is sitting on the referencing sheet and it doesn't have a name.
So we just want to use a direct cell reference to calculate the final price.
So we're going to take price multiplied with one minus the discount rate which is sitting on that sheet.
So to get that cell I can just click on that sheet name, switch to the sheet, and then click on the cell
that I need to include in my formula.
So notice what happens.
Excel automatically puts the sheet name in front of the cell name and it follows it by an exclamation
mark.
That's how Excel recognizes sheet names.
I need to fix this because I'm planning to pull my formula down so I'm going to press F4 and then close
the bracket and press enter
and that's it. I'm going to double click on the side and send the formula down.
If you have a space in the sheet name, Excel also adds this single quotation mark.
If you go to the referencing Tab, I'm just going to add a space somewhere in the middle, click away.
Let's go back to our original formula. Notice what happened.
There is exclamation marks still there but there are single quotation marks surrounding the sheet name.
That's because there's space in there.
If I remove that space, those quotation marks are going to disappear
but all of this is done for you by Excel.
You don't have to worry about putting the exclamation mark or putting those single quotations.
You just have to go to that sheet and click on the cell you want included.
Now what if your discount rate was sitting on another workbook. First thing you need to do is have the
other workbook open.
So let's say that this referencing sheet is actually in its own workbook.
So I'm going to right mouse click, create a copy from it so click move or copy, put a checkmark for create
a copy and let's send this to a new book. And then click on Ok.
So the default name here is Book2.
I'm gonna save this.
Let's go to desktop and save it in a folder called Master.
I'm going to call it Discount Rates.
OK so this workbook is now called Discount Rates.
Now let's switch back to our original workbook.
So using shortcut key control+tab, go to the place we want to write our formulas in.
Let's write them in here.
We're going to do price multiplied with one minus.
Now we need to go to our other workbook control+tab and click on the cell I need.
Now notice again Excel put the entire workbook and worksheet referencing by itself.
And it also fixed that cell reference, close bracket, press enter.
And that's that.
Let's send this down
and these are my discount rates.
So what happens if the other workbook is closed.
Is my formulas still going to run?
Let's test this.
Let's go to the discount rates and close the workbook.
My formula is still there and I see that the address has changed to the exact address of that workbook.
Now let's try one last thing.
Let's change the discount rate in that workbook.
So I'm just gonna open it again.
Let's change this to 50 percent, press enter.
Now immediately if I switch back I see that the discount rate has been applied
and if I close this workbook and save it my discount rate is still there.
So this is how simple it is to reference cells in other workbooks or other worksheets.