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
RayH - Jan 23, 2015 at 04:54 PM
Related:
- Add an X to cell if other conditions met
- Pdf and xps add in 2007 - Download - Other
- How to add an accent to an e - Guide
- How to add @ in laptop - Guide
- Add messenger to home screen - Guide
- How to add people on messenger - Guide
2 responses
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.
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"),"")
lancert
Posts
3
Registration date
Thursday January 22, 2015
Status
Member
Last seen
January 23, 2015
Jan 23, 2015 at 09:25 AM
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!
Thanks again for any help you can provide!
lancert
Posts
3
Registration date
Thursday January 22, 2015
Status
Member
Last seen
January 23, 2015
Jan 23, 2015 at 04:03 PM
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!
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!
RayH
>
lancert
Posts
3
Registration date
Thursday January 22, 2015
Status
Member
Last seen
January 23, 2015
Jan 23, 2015 at 04:54 PM
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.
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.
