Microsoft Office Tutorials and References

In Depth Information

**Creating conditional tests**

For example, suppose you type the formula
=[Form2.xlsx]Sheet1!F1
in cell A1 on Sheet1

of Form1 and then use Copy and Paste to copy this formula to cell B1. The formula in cell

B1 becomes =[Form2.xlsx]Sheet1!G1. The original formula changes when you copy it to cell

B1 because the reference to cell F1 is relative. However, if the formula in cell A1 of Form1

contained an absolute reference, such as =[Form2.xlsx]Sheet1!$F$1, the reference in the

copied formula would not change.

Copying and pasting between workbooks

When you copy a dependent formula from one workbook to another and that formula

includes a relative reference to a third workbook, Excel adjusts the reference to reflect the

new position of the formula on the worksheet grid. For example, suppose that cell A1 in

Form1 contains the formula =[Form2.xlsx]Sheet1!A1. If you copy and paste that formula

into cell B5 in Form3, the result is the formula =[Form2.xlsx]Sheet1!B5. Excel adjusts the

formula to reflect its new relative position. If, on the other hand, you copy a formula that

contains an absolute reference to another workbook, the formula remains the same after

you paste.

Even if you copy a dependent formula to the workbook to which the formula refers, it’s

still a dependent formula. For example, if you copy the formula =[Form2.xlsx]Sheet1!$A$1

from cell A1 of Form1 to cell A3 on Sheet1 of Form2, the resulting formula is essentially the

same, except that the book reference isn’t necessary because the formula is in the same

workbook. As a result, the formula becomes =Sheet1!$A$1.

Cutting and pasting between workbooks

Excel does not adjust the relative references in a formula when you cut it from one

workbook and paste it in another, as it does when you copy a formula. For example, suppose

that cell A1 on Sheet1 of Form1 contains the formula =[Form2.xlsx]Sheet1!A1. If you cut

rather than copy that formula and paste it into cell B5 of Form3, the references do not

change.

Creating conditional tests

A conditional test formula compares two numbers, functions, formulas, labels, or logical

values. You can use conditional tests to lag values that fall outside a given threshold, for

example. You can use simple mathematical and logical operators to construct logical

formulas, or you can use an assortment of built-in functions. For information about using

conditional test functions, see “Understanding logical functions” in Chapter 14.

You might also be able to satisfy some of your conditional curiosities by using the conditional

formatting feature in Excel. For details, see “Formatting conditionally” in Chapter 9.