Microsoft Office Tutorials and References
In Depth Information
USE GET.CELL TO HIGHLIGHT NON-FORMULA CELLS
To highlight every cell that does not contain a formula, use =NOT(HasFormula)
in the conditional formatting dialog.
Massive Gotcha: You cannot copy any cells that contain this formula to a
different worksheet without risking an Excel crash.
Breaking It Down: While most people typically use A1-style references, the
R1C1-style reference works better in the INDIRECT function. Normally, an
R1C1-style reference points to another cell. For example, =RC[-2] refers
to the current row and two cells to the left of the current cell. =R[10]C[3]
refers to 10 rows below and 3 columns to the right of the current cell. An R1C1
formula without any modifi ers, =RC, refers to the current cell. This is a case
where an R1C1 formula is far simpler than the equivalent A1 formula, =ADDRE
SS(ROW(),COLUMN(),4) .
Alternate Strategy: The advantage of using the method described above is that
the formatting will automatically update whenever someone changes a cell to
contain either a formula or a constant. If you simply need to get a snapshot of
which cells contain formulas, follow these steps:
Select all cells by pressing Ctrl+A.
Press Ctrl+G to display the Go To dialog.
Click the Special button in the lower-left corner of the Go To dialog.
In the Go To Special dialog, choose Formulas and click OK.
Choose a color from the Paint Bucket icon.
Additional Details: The complete list of GET.CELL arguments follows. Note
that in some cases, functionality has changed signifi cantly, and the argument
may no longer return valid values.
Part
I
1.
2.
3.
4.
5.
Argument
Returns
1
Absolute reference of the upper-left cell in reference, as text in the
current workspace reference style (usually A1 style , but it might be
R1C1 style if someone has chosen R1C1 style in their Excel Options
dialog).
2
Row number of the top cell in the reference.
3
Column number of the leftmost cell in the reference.
4
Same as TYPE (reference).
5
Contents of the reference.
6
Formula in the reference, as text, in either A1 or R1C1 style, depending
on the workspace setting.
7
Number format of the cell, as text (for example, “m/d/yy” or “General”).
8
Number indicating the cell’s horizontal alignment:
1 = General
2 = Left
Search JabSto ::




Custom Search