Tip #4470 - Calling On Other Worksheets
Printer Friendly Version | E-Mail This Tip
Calling
On Other Worksheets
I'm sure you've
all used cell references in your MS Excel formulas, right? You know,
let's say you want to add cells A2 and A3 and write a formula to do
this in another location. Maybe even something as simple as =A2 +
A3.
These formulas
are great and pretty easy to use, but let's say there's a piece of
data from one worksheet that you need to bring to another. What do
you do then?
If there's a chance
the number from the other worksheet could change, you don't want to
simply copy the number into the new worksheet. A move like that would
only cause you grief. Every time you make a change that altered the
value, you'd have to remember to retype the new number on the second
worksheet as well.
Forget it! That
method isn't worth the trouble.
Let's face it,
if you can't set your workbook up to run smoothly and keep updates
you have to make to a minimum, you're just looking for some trouble.
You'll inevitably overlook one of those repetitive updates and the
data will be meaningless.
So, now what?
The solution I
suggest is to use the cell locations from the other worksheets in
the formula, just like you would if the cells were located all on
the same sheet.
Okay, so it isn't
exactly the same. There is a slight difference in the way you reference
the cells, but once you understand the new references, it's smooth
sailing from there.
Now that we know
why we want to use references for cells from different worksheets,
let's get busy with the how to!
We all know about
the basic formula to add two cells from within the same worksheet
where the formula will be used. Let's use the one from above as our
example: =A2 + A3
Now, let's just
say that instead of A2 and A3 from the current worksheet, you want
to use A2 from Sheet2 and A3 from Sheet3 in the workbook.
The new formula
(with the different sheet references) would look like this:
=Sheet2!A2+Sheet3!A3
Your formula has
to somehow tell Excel where to find the cells in the workbook and
do it before the cell location with the sheet name and the
exclamation point. (Without the extra clarification, the
program simply uses the sheet with the formula).
After using a
formula like that, you're relieved from any extra updating! If you
change a number in either of those cells, the formula will automatically
update using the new values.
This type of referencing
works in any formula, but you have to be sure not to have any typos
in the sheet name. Excel will not guess what you mean, because it
only works very literally.
What's that? You
don't like all the extra typing? You're more of a "clicker"
when it comes to building your formulas?
No problem!
You already know
you can click to a cell location to insert it into a formula and well,
it works the same way here.
-
Start
your formula with the equal sign.
-
Use
the sheet tabs (or Ctrl + Page Up/Page
Down) to move to another worksheet in the workbook.
-
Click
on the cell(s) you need inserted into the formula.
-
At
this point, do not click back to the sheet you're
working on, just simply continue inserting the elements
(cell locations and keystrokes) of your formula.
-
When
you complete the formula, hit the Enter key.
You'll be returned
to the sheet you started with and your formula will be in place and
hopefully, working correctly.
Now that you know
how, feel free to call on all the worksheets in the book!
~ April
Want To Comment On This Tip? Click Here! We'd love to hear from you :-)
Like These Tips? Get 'Em Free In Your E-mail
