Determine the grading of a product and filter accordingly

[ntebaleng]

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 table that gives more details about the products available at the shop.

Product Code		Shelf life (days)	Date of Manufacture	Current date	Age of product (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/1/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

The question: the user want to be able to filter the table on sheet 2 based on the condition of the products.
For example,if the user want to vies only products that have expired,the following table must appear as results:

Select Condition	Expired
Product Code		Shelf life (days)	Date of Manufacture	Current date	Age of product	Volume(kg)
P103	Product 3	12	6/26/14	7/14/14	18	78
P106	Product 3	12	7/1/14	7/14/14	13	45
P107	Product 4	100	3/3/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

[p45cal]

In sheet1 of the attached are two versions of your Sheet1 table. The top version is your table where I have highlighted conflicting values (where, for example, for Product 1: what category does it get placed in if the product is 20 days old; 20 days appears in the Good AND bad categories. You have to decide which.
The second table is how I'd suggest you do things, have just a single number in each cell representing the thresholds only. I've highlighted those cells where I've had to guess the value you want in there. Note the shortened headers too.

On sheet2, there is also your table, with the added column H headed Condition. This column contains a formula along the lines of:
=INDEX(Sheet1!$B$9:$E$9,MATCH(F2,INDEX(Sheet1!$B$10:$E$14,MATCH($B2,Sheet1!$A$10:$A$14,0),0)))
This formula was actually derived from the cells in column M. Columns K:M (highlighted) don't need to be there anymore, they just show the steps taken to arrive at the formula; they can be deleted, there for your information only.

There is an Autofilter applied to the Condition column, but it's showing all the rows at the moment - I leave that for you.

If this is something you're happy to go along with, the formulae in column H can be made a bit more robust and easier to manage by using Named Ranges or Tables. You might also want to reduce the number of columns in this results table, since you won't need the Shelf life, the current date, nor the Age of Product since the formulae in the Condition column currently needs only to look at the Age of Product column and this can be worked out within the formula itself.

[p45cal]

Groan… you've cross posted without saying so.
Cross post: http://www.excelforum.com/excel-form...cordingly.html

ntebaleng, have a read of this: http://www.excelguru.ca/content.php?...-cross-posters
ExcelForum would/will want you to post links too, it's only netiquette.