A code that will determine the grading of a product and filter accordingly

**shazrn** · 07-15-2014, 02:52 AM

Hi,I am trying to determine an approach to follow in order to grade the product.Here is a detailed example

Sheet1: This is a reference sheet used to grade the condition of the product based on their ages
Eg,If product 1's age is 8 days then it is in 'Excellent condition'
Excellent condition(days) Good Condition(days) Bad Condition (days) Expired(days)
Product 1 1-10 11-20 20-25 26 or more
Product 2 1-4 5-7 8-9 10 or more
Product 3 1-6 7-10 11-12 13 or more
Product 4 1-40 40-80 80-100 101 or more
Product 5 1-35 36-45 45-55 56 or more

Sheet 2: This is the data table that gives more details about the products available at the shop.
Product Code Shelf life (days) Date of Manufacture Current date Age (days) Volume(kg)
P101 Product 1 25 6/29/14 7/14/14 15 34
P102 Product 2 9 6/30/14 7/14/14 14 55
P103 Product 3 12 6/26/14 7/14/14 18 78
P104 Product 4 100 5/13/14 7/14/14 62 12
P105 Product 5 55 6/13/14 7/14/14 31 34
P106 Product 3 12 7/01/14 7/14/14 13 45
P107 Product 4 100 3/3/14 7/14/14 133 56
P108 Product 5 55 6/12/14 7/14/14 32 12
P109 Product 1 25 5/24/14 7/14/14 51 32
P110 Product 2 9 7/12/14 7/14/14 2 39

What is required: the user want to be able to filter the table on sheet 2 by the condition of the products.
For example,if the user want to view all products in the shop that have expired,the following table must appear as results:

Condition to view: Expired

The spread sheet wil display only products that ahave expired
Product Code Shelf life (days) Date of Manufacture Current date Age(days) Volume(kg)
P103 Product 3 12 6/26/14 7/14/14 18 78
P106 Product 3 12 07/01/14 7/14/14 13 45
P107 Product 4 100 03/03/14 7/14/14 133 56
P109 Product 1 25 5/24/14 7/14/14 51 32

Please advise me on how i get excel filter the data in that manner.

Thank you