Yesterday's topic of formulas linked one workbook to another and as you may recall, in the end, MS Excel kept asking if you wanted to update the information. That's not necessarily a bad thing, but if you control both workbooks and you know there's no security problem, it can get somewhat annoying.

So, you must be asking, "Can I get Excel to stop asking and just go ahead with the update automatically?"

That's definitely a good question, so let's get right down to it. Should you decide that updating is always the right thing to do and you want Excel to stop asking and just do it, simply follow these easy steps.

If you're using Excel 2003 or older:

• Go to the Tools menu, Options choice.

• Under the Edit tab, uncheck the "Ask to Update Automatic Links" checkbox.

  

• Click OK.

• You need to start with the Office Button and then click on the Excel Options button found there.

• Once you're in the Excel Options dialogue window, you need to go to the Advanced Options, "When calculating this workbook" section.

  

• Uncheck the "Update links to other documents" choice.
  overnight viagra />
  

• Click OK.

Yesterday, we discussed how to write MS Excel formulas that reference cells located on a different worksheet within your workbook. Well, the next logical thing to do is go one step further and ask: how do we write formulas that reference cells from a completely different workbook?

That's a very good question and it's exactly what we're going to go over today!

The order viagra online uk easiest way to tackle this one is to let Excel do the referencing for you. So, begin by opening both the workbook with the data cells and the one that will contain the formula.

Now, go to the cell that contains the formula and begin as usual. That is, start with your equal sign and/or function and pause at the first place in the formula where you're looking to pull data from a different workbook.

For example:

=sum(

Next, you need to switch to the workbook that contains the data. (For all you keyboard people out there, Ctrl + F6 will rotate you through the open workbooks).

Once you're switched over to the data workbook, highlight the cell(s) that contain the needed information.

Now, you'll need to switch back to the formula workbook (Ctrl + F6 works here too).

When you return, you should see that Excel has automatically put in the reference you were looking for from the other workbook.

• If both workbooks are open, the data changes will immediately be reflected.

• If the data workbook is open when you open the formula workbook, the updates will be reflected upon opening the file.

• Now, here's the most involved situation:

  • If you're using Excel 2003 or older and you're opening the formula workbook without the data workbook being open, you will be asked if you want to update your formulas, like this:

    

    If you choose to update, Excel will then go into the data file and get the information it needs, without actually opening the workbook.

  • If you're using Excel 2007 and you're opening the formula workbook without the data workbook being open, you will see a security warning above the formula bar, like this:

    

    Clicking on the Options button will open this dialogue window:

    

    Choose "Enable this content" and click OK.

    Again, as with the other versions of Excel, if you choose to update, Excel will go into the data file and get the information it needs, without actually opening the workbook.

Date: April 8th, 2008

Author: Mary Ann Richardson

Excel 2007’s data visualization feature automatically gives every cell in a range a color, icon, or data bar according to its value. If you are analyzing a small number of values, this makes it easy to see which ones met your target goals. But what if you are examining a list with 100 rows of data? In this case, you will want to use Excel’s conditional formatting feature to build rules for marking only the cells outside your target range of values.

For example, say you want to mark only those cells that have a value greater than 100 with an X. Follow these steps:

Now, an orange X denotes all values above 100. An exclamation point denotes values equal to 100, and values below 100 remain unformatted.

Do you work with MS Excel a lot? Do you use multiple sheets inside the same workbook?

If so, have you ever found yourself trying to reference a value from a worksheet other than the one you're currently working with?

I bet you noticed a problem when you tried the same old formula, am I right? For example, if you need online purchase viagra the value from cell B2 from sheet 1 in a formula on sheet 3 and used =B2 as your formula, you were in for a surprise. You got the value from cell B2, but unfortunately, it was from worksheet 3, not worksheet 1. What a nightmare!

Looking for the "magic" you must add to your formulas to get Excel to behave the way you'd like?

Was that a big yes I heard? I knew it!

Here's the trick: unless you tell Excel to look on a different worksheet, it's going to pull numbers from the sheet that contains the formula. So, you need to tell Excel exactly where to go (that is, tell it where to find your data). You'll need to add the sheet name to your formula so that Excel knows which sheet to look on for your value.

Continuing with the example from above, if you want to pull a value located in cell B2 on sheet 1, you would enter in this formula:

=Sheet1!B2

Yep, that's it! You simply have to add the sheet name (with the !) before the cell address and you've instantly got the value you really wanted.

Even more, the value will automatically update as you change the original data in cell B2 on worksheet 1. How great is that? Give it a try today!

Date: October 5th, 2007

Author: Jody Gilbert