Microsoft KB Archive/30531

Cannot Extract with Computed Criteria in Excel PSS ID Number: Q30531 Article last modified on 02-26-1993 PSS database name: W_eXceL

2.00 2.01 2.10 3.00 | 2.20 2.21 3.00

WINDOWS | OS/2

Summary:

For computed criteria, the criteria field name must be a unique label. Do not use a database field name for a criteria field name. However, do use the database field names as the field names for the Extract range.

Please note that even if the database has multiple fields (such as Quantity On Hand and Quantity Sold), the criteria range has only one field name (for example, Test).

More Information:

Example Using Computed Criteria
The following example uses computed criteria to extract records from the database that have a greater In Stock value than Sold value.

To set up an illustration of using computed criteria, do the following:

  From the File menu, choose New.   Choose Worksheet and choose OK.   Type the following: A1: In Stock B1: Sold D1: Can Ship E1: A2: 5 B2: 2 D2: =A2>B2 E2: A3: 2 B3: 3 D3: E3: A4: 4 B4: 1 D4: In Stock E4: Sold   Select cells A1:B4.   From the Data menu, choose Set Database.   Select cells D1:D2.   From the Data menu, choose Set Criteria.   Select cells D4:E4. </li>  From the Data menu, choose Extract. </li>  Choose OK. </li></ol>

The extracted data will look as follows:

D4: In Stock E4: Sold D5: 5 E5: 2 D6: 4 E6: 1

The data for the items that have more In Stock than Sold is extracted.

Reference(s):

“Microsoft Excel User’s Guide,” version 3.0, pages 375-375

Additional reference words: 2.00 2.0 2.01 2.1 2.10 2.2 2.21 2.20 3.00 3.0

Copyright Microsoft Corporation 1993.