Add an X to cell if other conditions met

Closed
lancert Posts 3 Registration date Thursday January 22, 2015 Status Member Last seen January 23, 2015 - Jan 22, 2015 at 09:25 AM
 RayH - Jan 23, 2015 at 04:54 PM
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


Related:

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"),"")
0
lancert Posts 3 Registration date Thursday January 22, 2015 Status Member Last seen January 23, 2015
Jan 23, 2015 at 09:25 AM
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!
0
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"),"")
0
lancert Posts 3 Registration date Thursday January 22, 2015 Status Member Last seen January 23, 2015
Jan 23, 2015 at 04:03 PM
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!
0
RayH > lancert Posts 3 Registration date Thursday January 22, 2015 Status Member Last seen January 23, 2015
Jan 23, 2015 at 04:54 PM
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.
0