Tech Tips Home
The Best Tech Tips And Daily Deals
Newsletter On The Internet!

Shop online 24hrs a day or call us Mon-Fri
8:30AM-4:30PM EST - 1-800-915-2088
WorldStart Tech Tip And Store Search
Email: Password: Login Remember Me

Like what you see here? Subscribe to the Tech Tips newsletter!   Email: Subscribe

Calling On Other Worksheets

Thursday, December 27th, 2007 by | Filed Under: MS Excel, MS Office Help
 
Loading...


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

Leave a Reply


Like these tips? Get them for FREE in your email!

WorldStart's Tech Tips Newsletter

  • Tech Tips Daily - Become a tech pro! Get the very best tech and computer help sent directly to your email every weekday!

  • Tech Tips Weekly - If you don't want our Tech Tips newsletter every day, then sign up for this weekly newsletter to get the best information of the week. Sent on Fridays.

Other Newsletters

  • WorldStart's Daily Deals - Every week, we send out great deals in our Daily Deals newsletter. Many of these deals are exclusively for our Daily Deals newsletter subscribers and can't be found with our regular specials.

  • Just For Grins - Each issue includes a couple clean jokes, some funny quotes, and a hilarious reader's story. Newsletter is sent five days a week.


Enter Email Address:

Subscribe

Your e-mail address is safe with us!
We only use it to send you the newsletters you request. It is NEVER disclosed to a third party for any reason, ever! Plus, if you decided you don't like our newsletters (don't worry, you'll love them), unsubscribing is fast and easy.

Free Newsletter Signup



Tech Tips Daily

Become a tech pro! Get the very best tech and computer help sent directly to your email every weekday!

Tech Tips Weekly

The week's best in tech and computer help. Get your issue sent to your email every Friday!

WorldStart's Daily Deals

The very best deals on the Internet! Get a new set of incredible sales every day of the week!

Just For Grins

Clean jokes, funny quotes, and hilarious comics. Sent 5 times a week straight to your email.


Subscribe


Love Worldstart? Refer A Friend!

WorldStart's Premium Membership

Tip Archive


Categories:
Archives: