Allergen Map

[bdogg33830]

Im developing an allergen map of the food warehouse that i work. Each item has allergen numbers. Ranges from 0-13. Some may have more than one allergen. Some may have none. I want the map to identify if there is a placement error or just tell me where the best to put each item. The whole concept is the higher the number the lower it goes in the rack. Ive included a map example. It only applies to f02,f03,f06,f07 aisle. The others all are drive in slots and can only take one item.allergen map.xlsx

[JBeaucaire]

It is not evident from your sample workbook what you want done. Please create several complete examples, where you've filled in your reference data completely, then mockup manually the part you're wanting us to assist with a formula, and point out where you've done that so we know where to look.

[bdogg33830]

allergen map.xlsxThis is what i have so far with the complete item list and the slots that they are in. We have product delivered and taken from these slots each day. I need a way to store new product that we recieve in to comply with our allergen rules. So far I have come up with the map. But I dont know how to get excel to alert me that something is wrong with the way i have it stored. I wanted to use conditional formatting. But dont know how to write that formula. Also sorry that it took so long to get back with you.

[bdogg33830]

allergen map.xlsm New mockup of allergen Map. the formulas to the right of the map is what im using to control my conditional formulas. everything works fine except one issue. when i use an item in a2 that returns a 0 value in d2 i should be able to place that in any cell considering that item is an non allergen. im using an or function one part is true the other returns an error. i would think that since im using an or function and at least one part of it is true it would return a true or "YES". But it is returning an error. Whats the remedy in this situation.

[JBeaucaire]

My apologies, I'm not understanding what/where you want something to happen that is not happening.

You mention A2 and D2, I assume you mean on sheet Items To Put Up.
The code you have in A2 is 10015423.
The result in D2 is 0 as that is the correct code from the Allergen Product Code sheet.

So what is the issue?


--------
Side comment, your use of the function TODAY() in column E is problematic. That date will update every day you open the sheet, it's not a static date.

[bdogg33830]

In cell a2 is an example of a material id number (all material id can be found on Allergen Product Code with appropiate name and allergen code) with this particalular material id it doesnt have any allergens which is why it results a 0 in cell d2 of items to put up. So since it doesnt have any allergen i would like to put in any cell that doesnt currently have anything in it. All of the cells that i can put the material in should show green. but since this item has no allergen all cells that are not asign to a slot on page stock list should turn green. the conditional format that i used for cell j6 is in cell t6 and k6 is in cell u6 and so on. the error is in cells k8,j10 etc they should all turn green since that item doesnt have any allergen. in the formula i wrote i have an or statement where part of the formula is an error while the other part results in a true response but the formula as a whole is not. what i was asking is if i use an or statement if 1 arguement is true then it should result in a true reading. but thats not the case for what im doing. any suggestions

[bdogg33830]

also in the e column i want the date to update. the top macro paste the VALUE from cell a2 to e2 in the first row of sheet LT01 thanks

[karedog]

Hi,

Does simple wrapping all (every each one of them) the FIND() formulas with IFERROR() :

......, IFERROR(FIND(........), FALSE) .........

can solve your problem ?

[bdogg33830]

THANKS karedog problem solved.

[bdogg33830]

Final Resultsallergen map.xlsm

[karedog]

You are welcome bdogg33830, happy mapping