Databases Reference
In-Depth Information
TABLE 4.2
Fixed Formula Cell References
Fixed Cell Range Designations
Cell Ref
Col
Row
$A$1
Fixed
Fixed
A$1
Moving
Fixed
$A1
Fixed
Moving
A1
Moving
Moving
fixed. This allows each individual cell to set a format based on the value of the cell contained in
column 1 (
A
) of the current row. (Note: Cell references can be fixed in formulas by pressing the
F4 function key. Each push of the F4 function key will cycle through every permutation of formula
fixation. See Table 4.2.)
The formula {IF($A2
12,TRUE,FALSE)} in (4.7) checks to see if the value in column
A
of
the current cells row is equal to 12. If it is equal to 12, True is returned; else False is returned.
When this formula is utilized in the conditional formatting GUI, a value that defines the condition
for when the formatting is to be applied must be specified. This is why the “
=
TRUE” is appended
to the end of the formula. The conditional formatting shown in Figure 4.11 is then applied to cell
A2 within the Worksheet (row 1 contains the labels). Once this conditional formatting has been
applied to one cell, it can be applied to other cells utilizing the Format Painter tool shown in
Figure 4.12. Simply click on the cell that has the format desired, click on the format painter button,
and then drag the format across the cells desired.
Looking at Table 4.1, column 3 (C) is calculated utilizing column A and column B. The
resistance is calculated using Ohm's law:
=
v
=
i
R
(4.8)
which states that the voltage is equal to the current multiplied by the resistance. Rearranging
Equation 4.8 yields
v
i
R
=
(4.9)
Column C (Resistance) is therefore equal to column A divided by column B. Suppose that a
report is produced and, during the process of handling the report, someone accidentally writes a
static value to an entry in column C, replacing the formula. If a value in column A or column B
were to change, the corresponding value in column C would not change. Such a condition could
be very dangerous, especially when “what if” scenarios are to be run in the report. To eliminate
FIGURE 4.12
The Format Painter tool.
