Microsoft KB Archive/46458

-

{| 2.x 3.00 4.00 5.00 | 2.20 2.21 3.00 WINDOWS           |OS/2 kbusage The information in this article applies to:
 * width="100%"|


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
 * Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY
Using the Form command from the Data menu, you can redefine a Microsoft Excel database automatically to include new records as they are added. This is the recommended method when using databases with up to 32 fields.

For databases with more than 32 fields, the method below can be used. This method is paraphrased with permission from the Cobb Group's journal &quot;The Expert,&quot; (c), Vol. 2 No. 4, April 1989.

This method uses a defined name that refers to a formula to determine the lower-right-corner cell of your database.

To make your database autosizing (that is, to make it redefine itself to the number of entries in your database), use the definition

=tl:INDEX(tr:br,COUNTA(t:b)) where the following apply: tl = top left corner of database tr = top right corner of database br = reference far below bottom right corner t = cell that contains a field name that indicates a field that will have no blank cells b = column from t, row from br Before you enter this definition make sure that Database has been entered in the Name box of the Formula Define Name command. (If you are using version 5.0, choose Name from the Insert menu, then select Define.) Type the formula above into the Refers To box and press ENTER or click OK. Note: in Microsoft Excel version 5.0, these steps are not required because Microsoft Excel automatically highlights your list when you select Sort from the Data menu.

MORE INFORMATION
The COUNTA function counts the number of nonblank cells in the range t:b, so all database records should have something in this field; once a blank cell is reached, that should be the end of the database. This value, the number of nonempty cells, (that is, the number of records in the database) is used in the INDEX function to indicate which cell from tr:br (the rightmost column of the database) to return as the bottom right cell of the database.

For example, if your database were in columns A through E, and you had records defined in rows 2 through 89 (row 1 has the field names), you would use a database definition similar to the following:

=$A$1:INDEX($E$1:$E$1000,COUNTA($B$1:$B$1000)) Column B is assumed always to have an entry for any record in the database. Because a limit must be indicated, if the database extends beyond row 1000, the formula would have to be redefined, substituting a larger number for 1000 above. The larger the number used, the longer calculations will take; the smaller the number used, the smaller the absolute limit on the database size (until redefined). Note: Make sure that absolute references are used.
 * }

-

Last reviewed: November 4, 1994

© 1998 Microsoft Corporation. All rights reserved. Terms of Use.