Refer to a cell or range on another sheet

refer to a cell or range on another sheet

A 3D reference is one that includes a reference to one or more cells that is not on the same worksheet as the formula. For example, you might create a Summary sheet that pulls in totals from each of several other sheets in the workbook.

You're already familiar with the way area codes in phone numbers work, right? When you're dialing a number that's within your area code, you usually don't have to dial the area code. It's only when you're calling outside it that the area code becomes necessary.

It's the same thing with sheet names. If a formula refers to a cell on the same sheet, the sheet name can be left out; otherwise, it must be included.

Sheet names appear in formulas with an exclamation point after their names, like this:

So, for example, if I want to refer to cell A2 on Sheet2, the full way of expressing that is

Ranges work the same way. The range of A2:C6 on Sheet2 is

If you only want to repeat the value of whatever is in the cell, just precede it with an equal sign:

You can also use it in calculations, just like a normal cell or range. For example:

If you don't remember the sheet name and it isn't convenient to look it up, you can create a formula by selecting instead. Let's say, for example, that you want to create the preceding formula example by selecting. Here's how you would do it:

1. Click in the cell where the formula should appear, and type

2. Click the tab for the sheet containing the cells to which you want to refer. (In our example, that's Sheet2.)
3. Select the cells you want to refer to. (In our example, that's A2:C6.)
4. Press Enter. You're returned to your original formula, with the sheet name and range filled in.
5. Type ) -- the closing parenthesis -- and press Enter.

TIP
You can also use this process to refer to cells and ranges in other workbooks.