Microsoft Office Tutorials and References
In Depth Information
CELL
CEL CELL returns information about a cell’s location, formatting, or contents in the upper-left cell
in a reference.
=CELL(info_type,reference)
The CELL function returns information about the cell such as the format, or whether it’s general
or some type of number format. The CELL function is primarily for compatability with other
spreadsheet programs. Notice the following Table 7.1 with its descriptions, and then the
function results as shown in Figure 7.1. If the INFO_TYPE is “Format”, and the format of the cell in
the reference argument changes, the cell function will not update the value until the worksheet
is recalculated.
Here’s an example of the CELL function used to determine whether a cell is protected:
Y5:Y100 contains cell functions that determine the protection of D5:D100.Ex:
Y5=CELL(“protect”,D5) , Y6=CELL(“protect”,D6) , and so on.
To add up unprotected cells in a range in the form of an array the example could be:
{=SUM((Y5:Y100=0)*D5:D100)} , where the array entered formula will only add up the
unprotected cells in the range D5:D100 .
This is the text that tells Excel what kind of value your looking for. See
Table 7.1 for examples and descriptions.
INFO_TYPE
This is the cell in which you want information about. See Table 7.2 for
examples and descriptions.
REFERENCE
Table 7.1
INFO_TYPE Examples and Descriptions
Info Type
Return
Address
Reference of the first cell in reference as text
Col
The column number of the cell referenced
Color
Returns zero unless formatted in color for negative values, then it returns 1
Contents
Contents of the upper-left cell in reference
Filename
Full path and filename as text
Format
The text value of the number format in a cell
Parentheses
If the cell is formatted with parentheses, Excel returns 1; other wise it returns zero
Prefix
Text value of the label prefix
Returns ( ) for left-aligned text
Returns ( ) for right-aligned text
Search JabSto ::




Custom Search