30 September 2013

Excel References Between Sheets

Excel icon Sometimes you want to make a calculation in one Excel sheet and display the result in another sheet. In a new tutorial in the 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!

Excel Functions button

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. Tips and pointers

The 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 products but still useful to administrators of our applications.

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

By Peter Kalmstrom
CEO and Systems Designer Business Solutions