Microsoft Office Tutorials and References
In Depth Information
Using the Miracle Version of a Criteria Range
Using the Miracle Version of a Criteria Range
Using the criteria ranges in the preceding examples, you could easily build
any complex criteria with multiple AND or OR operators.
However, this could get complex. Imagine if you wanted to pull all the records
for five specific customers and five specific products. You would have to
build a criteria range that is 26 rows tall. Basically, the first row is the
headings for customer and product. The second row indicates that you want
to see records for Customer1 and Product1. The third row indicates that
you want to see records for Customer1 and Product2. The fourth row indic-
ates that you want to see records for Customer1 and Product3. The seventh
row indicates Customer2 and Product1. The 26th row indicates Customer5 and
Product5.
If you need to pull the records for seven customers and seven products from
five districts, your criteria range would grow to 246 rows tall and will
probably never finish calculating.
There is a miraculous version of the criteria range that completely avoids
this problem. Here s how it works:
The criteria range consists of a range that is two cells tall and one
or more cells wide.
Contrary to instructions in Excel help, the top cell of the criteria
range cannot contain a field heading. The top cell must be blank or
contain anything that does not match the database header row. For
example, you could put a heading of Computed Criteria.
The second row in the criteria range can contain any formula that
evaluates to TRUE or FALSE. This formula must point to cells in the
first data row of the database. The formula can be as complex as you
want provided the formula returns TRUE or FALSE. You can combine
AND, OR, VLOOKUP, NOT, MATCH, and any other functions.
For a simple example, suppose you want to find records that match one of 15
customers. You copy the customers to K24:K38. In the second row of the cri-
teria field, write the formula =NOT(ISNA(MATCH(A24,\$K\$24:\$K\$38,0)))
=NOT(ISNA(MATCH(A24,\$K\$24:\$K\$38,0))). This
formula does a MATCH on the first customer in the database to see if it is
in the list in K. The ISNA and NOT functions make sure that the criteria cell
returns a TRUE when the customer is one of the 15 customers.
Search JabSto ::

Custom Search