Hi All! I've always come here for help when I've not been able to find the answer online or in Youtube videos and it's never failed. I believe I have quite a tricky macro that I need to make here.
I've created an spreadsheet for creating recipes (Ejuice), as making them myself is not only healthier (as I know what's in it), but it's cheaper and I can make them with no nicotine. Here are the worksheets I have:
Recipes: Self explanatory, this tab holds my recipes and how to make them. When I create a new recipe, I copy a template in from the "Template" tab, enter all the flavors within the recipe, then enter the percentages of each flavor.
Template: Self explanatory. I copy this and paste it into the recipe tab when creating a new recipe.
Flavors: This sheet shows every single flavor concentrate (Column B), and shows how much I have of each flavor, in (ml) in column C.
What I want to happen is, when I make a recipe, I want to click a button and a macro will go into the "Flavors" worksheet and subtract (from my inventory) exactly what was used in that recipe. So for example, if that recipe used 5ml of Apple flavoring and 1.5ml of Bavarian Cream flavoring, it would go into my "Flavors" worksheet, find those flavors, and subtract the amounts in (ml) from what I have in my inventory. Sometimes I make 10 recipes at a time, and it can be extremely cumbersome to do this manually for 40 flavorings. All of the data is there, I just don't know how to combine it to make it do what I want. I'm happy to create another worksheet specifically for this macro, so that it is only doing inventory tracking for one recipe at a time; I think that would make it much simpler, though you guys are the experts, not me.
Document is attached, and I'll be MORE than happy to answer ANY questions that might help with the creation of this macro.
Flavor list and inventory amount in (ml) is: B2:C145
Recipe flavors are in column: B (Recipes worksheet)
ml value for each flavor in column: C (Recipes worksheet)
I already have a VLOOKUP formula beside each flavor (Recipes tab, column A) that searches the "Flavors" worksheet and let's me know if I have it in stock. Maybe this can help somehow?
Thanks so much in advance, you guys are honestly Rockstars and groomed me from being useless in excel, to quite proficient in it.
Bookmarks