Add an X to cell if other conditions met

[Closed]
Report
Posts
3
Registration date
Thursday January 22, 2015
Status
Member
Last seen
January 23, 2015
-
 RayH -
Hello,

I've got a list of products for an e-commerce site. What I'm trying to do in Excel is flag any items that have a quantity of zero AND are in one of 13 specific categories so I can add an X to the discontinued column so I know which ones are discontinued.

My thought is something like this but I'm not sure how to write the code in Excel to make it work:

=IF QuantityCell (D2) is equal to or less than 0 AND this product is in one of these range of 13 specific numbers (which are the category numbers) on a different worksheet within the same Excel file, then add an X to this cell to flag it as discontinued

Your help would be MUCH appreciated!

Thanks!

Lance


2 replies

Without seeing the actual layout you are working with it is difficult to find the exact solution.
However, I made some assumptions as to the locations of the fields you need.

Column B contains the Quantity.
Column C contains the ProductLine
Columns D through P contains the 13 ProductLines.

Put this in the discontinued cell and copy it down through the rest of the cells.

=IF(B2<=0,IF(ISNA(MATCH(C2,D2:P2,0)),"","X"),"")
Posts
3
Registration date
Thursday January 22, 2015
Status
Member
Last seen
January 23, 2015

Thank you for taking the time to reply! I guess I didn't give enough info for the locations of the data. I tried to use your formula and make it work but I didn't have any luck so I created some images that I hope will help:





Thanks again for any help you can provide!
The formula remained the same. only the range needed to be changed:

=IF(D2<=0,IF(ISNA(MATCH(B2,Categories!$A$95:$A$107,0)),"","X"),"")
Posts
3
Registration date
Thursday January 22, 2015
Status
Member
Last seen
January 23, 2015

Hi Ray,

Thanks again for your help. This seems to work for checking the quantities but seems to ignore the category number check, at least on my spreadsheet. I added the code to the cell and then if I change one of the category numbers on the category worksheet, it doesn't affect the results.

I may be doing something wrong on my end....

Also, is the value if true reversed?

Instead of:
=IF(D2<=0,IF(ISNA(MATCH(B2,Categories!$A$95:$A$107,0)),"","X"),"")

Should it be:
=IF(D2<=0,IF(ISNA(MATCH(B2,Categories!$A$95:$A$107,0)),"X",""),"")

Thanks!
>
Posts
3
Registration date
Thursday January 22, 2015
Status
Member
Last seen
January 23, 2015

Not sure why the issue exists at your end.
The formula I sent works on the setup I had to create.
The X is displayed in column E if the balance in column D is <= 0 AND the category in Col B is in the list on the Categories sheet.

If the category does NOT exist in the list despite the balance being <=0 then a blank is displayed.
Is this a srcenario that could happen? Is this the desired result?

upload your sheet to speedyshare and lets take look. Add some example results so we can weed out the bugs.