Microsoft Office Tutorials and References
In Depth Information
AUTO-NUMBER RECORDS AND COLUMNS IN AN EXCEL DATABASE
In cell A3, enter the formula:
=SUBTOTAL(3,B\$3:B3)
and copy this formula to the range A4:A13.
Notice that the ﬁ rst row reference is absolute and the second one is relative.
The formula in A13 is thus:
=SUBTOTAL(3,B\$3:B13)
In cell A2, enter the formula:
=IF(CELL("width",A1)=0,0,1)
In cell B2, enter the formula:
=IF(CELL("width",B1)=0,0,MAX(\$A2:A2)+1)
and copy it to the range C2:G2.
Notice that the ﬁ rst column reference is absolute and the second one is relative.
The formula in G2 is thus:
=IF(CELL("width",G1)=0,0,MAX(\$A2:F2)+1)
Now you are set. Breaking It Down: Let’s start with the auto-numbering of
the records. The SUBTOTAL function has the syntax SUBTOTAL (type,ref). In
your formula, you speciﬁ ed type as 3 , which is the equivalent of the COUNTA
function. Column B contains names and thus qualiﬁ es for use of this subtotal
type because the COUNTA function counts the number of text values in a range.
The formula makes use of the fact that SUBTOTAL excludes hidden cells from
its calculation.
Consider the formula in A10: =SUBTOTAL(3,B\$3:B10) . The total number of
text values in this range is eight, but only ﬁ ve values are visible, so the formula
returns 5 , which is the serial number you want!
For auto-numbering of the columns, you use the CELL function. The syntax
for this function is CELL(info_type, [reference]) . When you specify
" width " as info_type , the function returns the column width of the top left
cell in reference.
The formula in cell A2, =IF(CELL("width",A1)=0,0,1), returns 0 if
column A is hidden and 1 if it is not.
To understand how the formulas in B2:G2 work, ﬁ rst consider the formula in
D2:
=IF(CELL("width",D1)=0,0,MAX(\$A2:C2)+1)
Because CELL("width",D1)=0 (column is hidden), the formula evaluates
to 0 .
The formula in the cell in the next visible column, F2, is:
=IF(CELL("width",F1)=0,0,MAX(\$A2:E2)+1)
Part
I

Search JabSto ::

Custom Search