Microsoft Office Tutorials and References
In Depth Information
Referring to Other Worksheets
Copying Formulas
Referring to Other Worksheets
To reference a cell in another worksheet, you simply need to preface the cell with
the worksheet name, followed by an exclamation mark. For example, say you’ve
created a formula to double the value of the number in cell A1 on a worksheet named
Sheet1. You’d use this formula:
=A1*2
If you want to use the same formula in another worksheet (in the same workbook),
you’d insert this formula in the new worksheet:
=Sheet1!A1*2
Note: If you use the point-and-click method to build formulas, you’ll find that you don’t need to worry
about the syntax for referring to cells on other worksheets. If you switch to another worksheet, and then
click a cell in it, while you’re building a formula, Excel automatically inserts the correct reference with the
worksheet name.
FreqUently Asked qUestion
How Changing the Location of Cells Affects Formulas
OK, I know how Excel adjusts a formula when I copy it to
another location. But what happens if I move cells around
after I’ve created a formula?
If at any time Excel can’t find your cells, the formula
changes to show the error code #REF!. You can then take a closer
look at the formula to find out what really went wrong. For
example, if you delete column B from your spreadsheet (by
selecting the column and using the Home➝Cells➝Delete
command), the formula changes to this:
No worries. It turns out that Excel is surprisingly intelligent.
Consider the following simple formula:
=B1+A2
If you cut and paste the contents of A2 to A3, Excel
automatically updates your formula to point to the new cell,
without complaining once. It also performs the same
automatic cleanup if you drag the contents of a cell to another
location (although if you simply make a duplicate copy of
the cell, Excel won’t change your formula). Excel is also on
the ball when you insert and delete rows and columns.
=#REF!+A2
Even though there’s still a B1 cell in your worksheet (it’s
the cell that was formerly named C1), Excel modifies the
formula to make it clear that you’ve lost your original data.
 
Search JabSto ::




Custom Search