Search list for known values

Well I replied in the main thread, my bad. I am new at this. To answer your question all informationcomes from a access database that runs queries and then exports that information to the MainSheet and also exports the spreadsheet database list. If you open the mainsheet and read what wrote there and then goto databaselist it should clear things up for you.

its not "Item Description", there is no column named that, its "ItemNumber" as stated in the spreadsheet.

I apologize Item Description does exist, it comes from mainsheet along with the rest of the values.

Those values in the example are a portion of the spreadsheet "MainSheet" located in the other tab on bottom, those values are brought in via a database. I just copied it from there to use an example. I am sorry for the confusion, hope this clears it up for you. And I really appreciate you help.

Is there any experts out there that can help me, please I really need to complete this project soon. Thank you very much.

Yes it could be, it is the formula name for the ItemNumber, though it is not unique as the ItemNumber is, due to the fact that there are different ItemNumbers that are also called by those formula names, due to different customers ordering the same item. If I need to change the left most column to a unique identifier that should not be a problem.

I have revised the spreadsheet, you will find hopefully more information on what I am needing, and I tried to explain the information a little better, what this is, is a spreadsheet that is linked to an access database; from the access database I retreive work orders via my work intranet site, then I paste the workorders to the MainSheet of the spreadsheet, and then take that same information and put it through an access query or two to link needed information to the ItemNumbers, i.e. approved follow information, and then export that to the Databaselist sheet. hope this helps.

Here is the revised version of my workbook.

Charles,

First of all thank you for sticking with me, I have made a complete mess of things, I'm starting to confuse myself with this thing.
Anyway if you don't mind, I am going to revamp this Workbook and then reattach it with what I believe will clear up some fog. This may take me some time, so again I appreciate your patience with me.

Check back tomorrow night, it should be completed by then, if not I will make a reply.

Thank you....

Hope this one helps you help me. I am out of ideas on how to explain what I'm needing here.

Thank you Charles.

Charles,

Yes the finished sheet is where I will paste the lineup and then press the button to cross reference the approvedfollow, and the preventfollow sheets.

Charles,

I appologize in advance but I have forgotton two key columns in the PreventFollow sheet, I am going to re-upload the workbook with the changes.

Just to explain it: Some Formula or ItemNumber's may contain extra information concerning the PreventSpecies, That will be found in the last two columns, PreventContaining, and the next which is just the unit of measure used, ie.. grams per ton or GRT.

So for instance it may say that a Formula has a prevent due to whatever Additive, and if it contains extra information in the PreventContaining column then it is limited to that Additive.

PreventContaining column is a list of Additives if that Formula is prevented from mixing before or after a Species contiaining that particular Additive, otherwise if the Formula is in the PreventFollow sheet it is prevented from mixing before or after what ever the PreventSpecies is, like stated before on the PreventFollow sheet.

Sorry about this, it slipped my mind..

Charles,

Here is my Code for my worksheet, minus all that I need you to figure out about the Approved / Prevent stuff.

Anyway thought maybe this would help.

What I do is Copy the Lineup, then press pastelineup on the MixerLineup sheet and it does its thing, it works, but of course it needs to do so much more.

Forgive my sloppy VBA skills.

Just a question, when I delete the current lineup by pressing delete, it does delete all the sheets, but I have to close the workbook and then reopen it inorder to get the paste button to work again on another lineup? Maybe you could shed some light on how to fix that.

Charles,

Yes its a good follow. If the ApprovedLifeStage or ApprovedProductType say "ALL" Then as long as the Species is Approved, it doesnt matter about the LifeStage or the ProductType.

Charles,

The reason I didn't put anything next to the 2nd row, is because that is the first run as long as I dont mix anything after it that would be in the PreventFollow sheet its good to go.

Charles,

They don't belong in the Approved or the Prevent because they do not contain any additives. If an ItemNumber does not contain an additive it will not be in those sheets.

Charles,

Since 14TM is a dairy, you are not aloud to run 3152 before or after dairy feed because it contains CTSM; which "Dairy" is in the preventfollow sheet.

If a product is in the preventfollow sheet, look at the preventfollowspecies and if the species is before or after that product located in preventfollow sheet both it and the product in the preventfollowspecies column are considered bad follow. Hope this helps.

Charles,

You are absolutely right, there is no way to know that, I apologize, didn't even think of that, I will amend the worksheets and repost it.

Again I apologize. Thank you for your work so far, ill download the workbook and check it out later, I'm on my cell right now.

Charles,

I tried the workbook code and it seem to work, but when I change the formula to a known prevent follow it fails to say bad follow...

Here is the updated workbook, with the needed information.

Thanks for all your hard work. It is greatly appreciated..

Charles,

I was taking a look at your code, you do know that the lineup will not always be the same, some days it may have swine in it, or chicken, pheasant, or what ever. Every time the lineup changes and I paste it to the MixerLineup sheet, it will automatically be queried with the information needed for the ApprovedLineup sheet and the PreventLineup sheet, if anyone of those formulas do in fact contain an Additive.

So with that, the Species of the Product on the lineup will not always be a "Cattle", or a "Dairy", so the if statements for "Cattle", "Dairy"; someday maybe for "Swine", ect.... depending on what formula is run that day.

I hope I am not adding a whole lot of problems to the mix, and maybe Im reading the code wrong that is entirely possible, for I am no VBA expert like yourself.

Charles,

Like I said in my last posting, the MixerLineup will not be the same, each day a new Lineup comes out, and each day I will paste it to the Finished sheet and run the code.

Anyway I wrote all this in the last post. Im not sure why it didnt work. I think it has something to do with the parameters that the code is working under, for instance.

If I run a dairy with no additives, then after try to run a dairy that contains an additive that has dairy in its preventspecies column; both of those rows should say bad follow, since dairy is prevented from following a product that contains an additive that has dairy as its preventspecies, it is also prevented from being run before that product as well.

This doesnt just apply to dairy, what ever the product may be, if it contains an additive and has a preventspecies, that species cannot run before or after that product(Probably should have renamed PreventFollow to something like PreventBeforeAfter), anyway, as far as approvedfollow goes that only has to do with following the product that contains the additive, if a product does contain an additive, the only approvedfollow product must fall into the parameters of the approvedfollowspecies, ...followlifestage,...followproducttype...

Charles,

To answer your question, "What did you change?" I changed the Formula code above the 3152, to another product formula name which should have triggered a badfollow, but it said that it was good?

Like I said, not sure why?

I changed it to IJ36.

I tried a dairy (1j36) before the 3152 and then after it, and it said goodfollow.

hey,

Is the formula the unique identifier, it should be the trigger to find what the species is and to see if it is in the approvedfollow and or preventfollow if it is to be run after a product that contains a additive, and it that additive contains the productspecies name of the product that is to run around it, in the preventspecies column it should read bad follow.

Hey Charles,

example:

1J36(DAIRY)
3152(CATTLE, CONTAINING CTSM; DAIRY IS A PREVENTSPECIES FOLLOW) [BadFollow] - Dairy is prevented from running before or after any product that contains CTSM!

Charles,

14tm as a product does not belong in the approved or the prevent sheets, because it does not contain an additive. Only products that contain additives will be found in those sheets because the approved sheet shows what products can run after those products that contain additives, the preventfollow sheet is a list of those products that contain additives that are at a level or potency that some products are stricken from running before and after so they are considered a preventfollow.

The information sheet shows all products on the lineup so you can see what each product is.

If you have any questions, ill be more than happy to answer them. Thanks again.

Charles,

Thank you, but if I change the formula below the 3152 which contains CTSM both the 3152 and the dairy formula I placed below it should read bad follow.

This lineup will change daily, so today it may have looked like this, tomorrow it may not, the rows must respond to what additive the product may or may not have, and whether or not that product that is placed around the one with the additive is an approved follow or not.

Charles,

The Formula of these products don't matter, I can run a dairy before or after a cattle or vice-verca, what matters is if a product contains an additive and if it does, whether or not the products run before and after are not in the preventfollow and are found in the approvedfollow if they are to follow the product containing the additive.

So I should be able to change the Formula name to a formula that is on the current lineup and knowing that it can not run before or after a formula that contains CTSM because it is a dairy, after pressing the button, both the dairy and the product containing CTSM(ctsm is just an example, could apply to any other additive,but that will be desplayed in the follow sheets) should have colored red or in this case said "Bad Follow"

Charles,

I revised the Workbook again, added another column to the lineupInformation sheet. And I put a note on the FinishedLineup sheet; its acting a little funny, try the example on the FinshedLineup sheet, see what you think.

Thanks again..

cant understand , please do more

Charles,

That worked good, but when I changed the formula below the one with CTSM to a dairy formula it again said goodfollow.

Not sure how you are calculating whether or not the formulas above or below can run around a product containing an additive, let me know please, or are you just running the code on those few rows for practice?

Charles,

Is it looping through every row? Because I try to change some of the formulas on different rows and it doesnt work right.

Thanks Charles for your help.

Ok I will check it out, thanks....

Charles,

I tried the code, it looks good until I changed the first formula to 3152, then 30AC which contain an additive that dairy is in the prevent, and it said good, also the third formula did the same and the next to last also, if the product before and after is in the prevent of the formula in the middle all of them should read badfollow..

Here are my findings.

Charles
Yes you are correct, not sure why 13dw was in there, and you're also right that 361b has no prevent for dairy, sorry about that. How's the coding going other than me adding headaches with my mistakes? If you have updated the code, i would like to check it out.

Well you have a happy thanksgiving, talk to you soon.

Sure ill send it in few hours.

Here you go.

No I was wrong again. I looked over the workbook and Re did the finished sheet all is good now.

Yes I attached it.

Yes I know, I was wrong about that, I revised the workbook and resent it earlier. I redid the finished sheet with the corrections.

No Dairy is not in the Prevent for 361b, i made a mistake there, sorry. 361B does not have dairy as a prevent.

But on the other hand, dairy is not an approved follow for 361b!

Because, if you go to approvedfolow sheet and find 34A0 you will see that it contains several additives, one of the additives is fish oil and on fish oil dairy is not an approvedfollow, hence dairy can't follow 34A0.

Charles,
To answer your question about knowing whether or not a product is not an ApprovedFollow, lets take the dairy and fish oil problem, since we are referencing the product 361B and it contains Fish Oil(among other additives), first we find the product name 361B in the ApprovedFollow sheet and find the Additive(s) it contains and look over to the ApprovedFollowSpecies, Lifestage, ProductType columns, if Dairy or whatever productspecies you are trying to follow the one with Additive(s) with is not in those columns then it is not approved to follow it, only productspecies that are found in those columns are aloud to follow the product containing that Addditive.

Charles,
I don't think you fully understand what I am wanting to accomplish here. I will do my best to explain. I start with a list of information(which is a lineup) that has to be mixed. On this lineup which I will receive a new one each day, are several different types of animal feed, but the problem is we cant just mix it in any order, due to federal regulations, it must follow a strict order so as not to contaminate the animals and the end consumer.

So knowing that each product is different(there are sometimes several runs of the same product, for different work orders) anyway not all products will contain an additive.

Additives are the key to whether or not another product can mix before or after the product containing the Additive: In order to know whether or not a product does contain an Additive, we use Formula Names to reference the type of product it is, so that is the importance of the Formula.

Additives are the main focus of this spreadsheet: I want to be able to take the lineup from "MixerLineup" sheet, bring it into the "FinishedLineup" sheet and be able to cut and paste rows to see whether or not that particular product can run in that position. This is where the code comes into play.

WHAT THE CODE SHOULD DO:
The code is supposed to check the left column starting in the second row find the Formula name, check(lets say for sake of argument, because I'm not sure how you are referencing if it contains an additive or not)the "LineupInformation" sheet to see whether or not it contain contains any additives and if it does, then go to the "PreventLineup" sheet to see if the product that is to run before and after it is located in the "PreventFollowSpecies" column(if the species of the product to run before or after the product that contains the Additive(s) is found here then both those products and the product containing the Additive(s) are not allowed to run around one another, therefore should be highlighted red. (Bad Follow)

If they are not in the "PreventFollow" sheet then the product before the product containing the Additive(s) is good, but we must now reference the "ApprovedFollow" sheet, to see whether or not the product (species, lifestage and producttype) are a match to the ("ApprovedFollowSpecies", "ApprovedFollowLifestage" and "ApprovedFollowProducttype") (sometimes the "ApprovedFollowLifestage" and the "ApprovedFollowProductType" says "ALL" in this case as long as the Species is found in the ApprovedFollowSpecies column) it is approved to follow the Product containing the additive(s), (but we must reference all additives the product may be containing, to see whether the follow product is approved to follow the product that contains the Additive(s)! (Some products contain several Additives, if one of the additives do not have the follow product in its (ApprovedFollow columns) then that product is not an approved follow), therefore that product after the product containing the Additive should be highlighted red, it is a bad follow.

I hope this clears any confusion up, if not feel free to ask. Talk to you later.

I am not sure how to make all this happen, I just know what needs to happen, thats why I came to the excel experts. I wasnt even sure this could be accomplished in the first place. Hey but you are doing such a good job. I thank you...

charles,

Hows it going?

Charles,

Here are my findings, I placed a Note on the FinishedLineup sheet.

thanks...