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