Hello all,
I am working on a problem in excel that I believe involves searching, matching, repeating, and finding. Below you will find a brief explanation of what I am trying to work on, however it makes much more sense when viewing the attached excel file.
I am working on creating a document that automatically produces the ingredients necessary for certain recipes in a designated area of my workbook. On the far left of the workbook in Columns A-M, there is a table that allows me to select when I would like to eat each recipe. There is a list of 10 Recipes in Column A. If I would like to eat Recipe 1 on Day 1, there is a number 1 beside Recipe 1. If I would like it on Days 1,2, and 3, a "123" would be found beside Recipe 1 which you can see in Cell B3. Given that there are 3 days I would like to make Recipe 1, this means I would like 3 servings which you can see as the total in Cell C3.
I also have the recipes broken down by when an individual could "Meal Prep". For instance, If they wanted to cook for Days 1-3, and then days 4-5, and lastly for days 6-7 in a given week, there would be three meal prep days. We will call these 1.1, 1.2, andn 1.3 which you can see in Cells B2, D2, and F2.
My first question is to figure out how to create a list of recipes that I will be using for Meal Prep days (1.1, 1.2, 1.3) without manually inputting them based on my recipe selection. I am able to input the days in which I would like to create the recipes in Column B-L, and I would like my selection here to automatically generate a list of the recipes in Columns AA to AF. You can see in Columns AA to AF, I manually input the correct answers. Is there a way to find all of the recipes in Column B that contain numbers and then input the recipe names in Column AA as shown in the excel file? I would be able to use this same pattern for columns AB to AF as well.
The second part of the problem is more complicated. In column Q I have a list of the recipes and their corresponding ingredients. In column P, I have the original amount of each ingredient per serving. In columns R to W, the number of servings present for each meal prep day are shown.
What I would like to do is take the first recipe found in Cell AA2, and input that in Cell AJ2. In my example, this is "Recipe 1." I then would want to automatically populate the ingredients that correspond with Recipe 1 in Cells AK3-5 which you see in my chart (eggs, ham, bacon). In cells AJ3-5 the amount is correct because the original recipe calls for 2 eggs in column P, but and because we are working with 1.1, there are 3 servings in column R. This gives us 6 eggs in cell AJ3. If I were to change my input in cell B3 and made it blank, I would expect Cell AA2 to display Recipe 2, and cell AJ2 to display Recipe 2, with the ingredients and serving amount for Recipe 2 in AK 3-5 and AJ 3-5.
I can repeat this process in Column AJ for each recipe in column AA. Then I will have all of the 1.1 recipes complete. I can then move on to 1.2, 1.3 and so on using the same idea to automatically populate the ingredients that I will need with the correct serving sizes based on the recipes that I select in the very far left table.
Your help is greatly appreciated!!!
Bookmarks