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.
Search JabSto ::




Custom Search