Excel References Between Sheets

Sometimes you want to make a calculation in one Excel sheet and display the result in another sheet. In a new tutorial in the kalmstrom.com Tips section I am showing how to make a formula refer to another sheet than the current one.

Exclamation mark
As an example in my tutorial I use a small ice cream factory with three kinds of ice creams. In an Excel workbook they have one tab for calculation of the selling price for each kind of ice cream. On another tab they have the costs for all ingredients of each type of ice cream.

When the costs of the ingredients for a certain batch are summarized and divided with the number of ice creams in that batch, they get the cost per ice cream. To see how much profit they will get with different prices, they need to get the cost per ice cream into the price calculation sheet.

This is actually very easy. When you create the formula and pick the cell for cost per ice cream in the Recepies sheet, Excel will automatically add not only the cell but also the name of the tab and an exclamation mark. The exclamation mark is the key!

Works with more
My example is very simple, but the principle is the same in more advanced use of references from outside the sheet: if you know how to manage it, Excel will help you with the formulas.

You can go to another workbook and pick a cell there to include in your formula, and you can refer to a range of cells or to a name. I will show this in a later tutorial about Lookup references.



kalmstrom.com Tips and pointers
The kalmstrom.com website has a much visited Tips section with tutorials on the Microsoft products we build on, like Excel, SharePoint and Outlook. They are not directly connected to the kalmstrom.com products but still useful to administrators of our applications.

Obviously many others also find these tutorial interesting, because the kalmstrom.com Tips section has a lot of visitors. And you are all welcome!

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions