SOLVED
Home

What Formula to use to pull data from drop down list?

%3CLINGO-SUB%20id%3D%22lingo-sub-215659%22%20slang%3D%22en-US%22%3EWhat%20Formula%20to%20use%20to%20pull%20data%20from%20drop%20down%20list%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215659%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Morning%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20someone%20help%20me%20out%20here%3F%20I%20have%20been%20racking%20my%20brain%20for%20the%20last%20two%20hours%20trying%20to%20figure%20this%20out.%20What%20I%20am%20a%20trying%20to%20do%20is%20when%20I%20chose%20an%20item%20from%20the%20a%20drop%20down%20list%20is%20would%20put%20the%20corresponding%20data%20to%20that%20item%20next%20to%20it%20in%20the%20same%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20make%20an%20easier%20way%20to%20create%20a%20weekly%20menu%20for%20my%20family.%20Because%20we%20are%20on%20a%20strict%20limit%20of%20what%20we%20can%20eat.%20It%20is%20important%20that%20we%20understand%20how%20much%20each%20item%20has.%20This%20way%20when%20I%20chose%20a%20recipe%20for%20that%20day%2Fmeal%20period%20when%20the%20day%20is%20done%20it%20shows%20my%20total%20mounts%20for%20that%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20we%20can%20only%20have%20a%20total%20of%20135g%20of%20fat%2C%20115g%20of%20protein%20%26amp%3B%2020g%20of%20carbs%20a%20day.%20So%20after%20choosing%20all%20the%20recipes%20for%20that%20day%20for%20breakfast%2C%20lunch%20and%20dinner%20plus%20snacks%20it%20must%20not%20exceed%20the%20amount%20above.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20data%20is%20simple%20and%26nbsp%3Bthat%20if%20I%20made%20a%20spreadsheet%20that%20it%20would%20be%20easier%20to%20create%20a%20menu%20that%20does%20the%20math%20for%20me%20than%20me%20having%20to%20pull%20out%20a%20calculator%20and%20doing%20the%20math%20and%20having%20to%20change%20recipes%20to%20get%20me%20at%20or%20below%20the%20numbers.%20If%20have%20an%20suggestions%20or%20what%20formula%20I%20could%20use%20to%20achieve%20my%20goal%20I%20would%20greatly%20appreciate%20it.%20Thank%20you%20in%20advance%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20what%20my%20data%20looks%20like%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EFat%3C%2FTD%3E%3CTD%3EProtein%3C%2FTD%3E%3CTD%3ECarbs%3C%2FTD%3E%3CTD%3EFiber%3C%2FTD%3E%3CTD%3ENet%20Carbs%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ECalories%3C%2FTD%3E%3CTD%3E(grams)%3C%2FTD%3E%3CTD%3E(grams)%3C%2FTD%3E%3CTD%3E(grams)%3C%2FTD%3E%3CTD%3E(grams)%3C%2FTD%3E%3CTD%3E(grams)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ELocation%3C%2FTD%3E%3CTD%3EName%3C%2FTD%3E%3CTD%3EServings%3C%2FTD%3E%3CTD%3Eper%20serving%3C%2FTD%3E%3CTD%3Eper%20serving%3C%2FTD%3E%3CTD%3Eper%20serving%3C%2FTD%3E%3CTD%3Eper%20serving%3C%2FTD%3E%3CTD%3Eper%20serving%3C%2FTD%3E%3CTD%3Eper%20serving%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPg.%20216%3C%2FTD%3E%3CTD%3EKedough%20Pizza%20Crust%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E414%3C%2FTD%3E%3CTD%3E48.7%3C%2FTD%3E%3CTD%3E22.5%3C%2FTD%3E%3CTD%3E1.5%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E1.5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPg.%20225%3C%2FTD%3E%3CTD%3ESausage%20Gravy%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E380%3C%2FTD%3E%3CTD%3E35%3C%2FTD%3E%3CTD%3E15.2%3C%2FTD%3E%3CTD%3E1.9%3C%2FTD%3E%3CTD%3E0.2%3C%2FTD%3E%3CTD%3E1.7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPg.%20236%3C%2FTD%3E%3CTD%3EFaux-Gurt%20(opt.%201)%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E433%3C%2FTD%3E%3CTD%3E45%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPg.%20236%3C%2FTD%3E%3CTD%3EFaux-Gurt%20(opt.%202)%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E248%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPg.%20250%3C%2FTD%3E%3CTD%3EVanilla%20Coffee%20Creamer%3C%2FTD%3E%3CTD%3E2T%3C%2FTD%3E%3CTD%3E77%3C%2FTD%3E%3CTD%3E7.2%3C%2FTD%3E%3CTD%3E0.6%3C%2FTD%3E%3CTD%3E0.9%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0.9%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPg.%20256%3C%2FTD%3E%3CTD%3EChedder%20Cheese%20Chips%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E228%3C%2FTD%3E%3CTD%3E18.5%3C%2FTD%3E%3CTD%3E14%3C%2FTD%3E%3CTD%3E0.7%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0.7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPg.%20257%3C%2FTD%3E%3CTD%3ECrispy%20Pepperoni%20Chips%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E60%3C%2FTD%3E%3CTD%3E5.4%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPg.%20258%3C%2FTD%3E%3CTD%3EBLT%20Boats%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E269%3C%2FTD%3E%3CTD%3E24.6%3C%2FTD%3E%3CTD%3E9.5%3C%2FTD%3E%3CTD%3E2.6%3C%2FTD%3E%3CTD%3E0.8%3C%2FTD%3E%3CTD%3E1.8%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-215659%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ERequests%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-215673%22%20slang%3D%22en-US%22%3ERe%3A%20What%20Formula%20to%20use%20to%20pull%20data%20from%20drop%20down%20list%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215673%22%20slang%3D%22en-US%22%3E%3CP%3EArul%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20the%20quick%20response.%20That%20is%20exactly%20what%20I%20am%20trying%20to%20do.%20This%20will%20make%20it%20easier%20to%20create%20my%20daily%20planning.%20Again%20thank%20you%20so%20much.%20~Steve%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-215668%22%20slang%3D%22en-US%22%3ERe%3A%20What%20Formula%20to%20use%20to%20pull%20data%20from%20drop%20down%20list%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-215668%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20need%20something%20like%20in%20the%20file%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20insert%20data%20in%20the%20DATA%20sheet%20(obv).%20Be%20careful%20to%20not%20switch%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20when%20you%20need%20to%20check%20numbers%2C%20use%20the%20CHECK%20sheet.%3C%2FP%3E%3CP%3ESelect%20from%20the%20dropdown%20menu%20(that%20you%20need%20to%20update%20-data%2C%20validate%20cell%2C%20list%2C%20select%20the%20new%20interval)%20the%20dish%20you%20want%2C%20it%20shows%20you%20aside%20the%20values%20from%20DATA.%20You%20also%20need%20to%20update%20sheet%20CHECK%20if%20you%20add%20more%20receipes%20(to%20get%20all%20cells%20in%20the%20formula).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20top%20of%20the%20page%2C%20it%20sums%20all%20the%20datas%20from%20the%20dishes%20you%20chose%20and%20alerts%20you%20if%20something%20is%20wrong.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20not%20delete%20data%20from%20grey%20cells%20and%20do%20not%20erase%20rows%20even%20if%20you're%20seeing%20it%20blank.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Steve French
New Contributor

Good Morning,

 

Could someone help me out here? I have been racking my brain for the last two hours trying to figure this out. What I am a trying to do is when I chose an item from the a drop down list is would put the corresponding data to that item next to it in the same row.

 

I am trying to make an easier way to create a weekly menu for my family. Because we are on a strict limit of what we can eat. It is important that we understand how much each item has. This way when I chose a recipe for that day/meal period when the day is done it shows my total mounts for that day.

 

For example we can only have a total of 135g of fat, 115g of protein & 20g of carbs a day. So after choosing all the recipes for that day for breakfast, lunch and dinner plus snacks it must not exceed the amount above.

 

The data is simple and that if I made a spreadsheet that it would be easier to create a menu that does the math for me than me having to pull out a calculator and doing the math and having to change recipes to get me at or below the numbers. If have an suggestions or what formula I could use to achieve my goal I would greatly appreciate it. Thank you in advance for your help.

 

Here is what my data looks like:

    FatProteinCarbsFiberNet Carbs
   Calories(grams)(grams)(grams)(grams)(grams)
LocationNameServingsper servingper servingper servingper servingper servingper serving
Pg. 216Kedough Pizza Crust441448.722.51.501.5
Pg. 225Sausage Gravy63803515.21.90.21.7
Pg. 236Faux-Gurt (opt. 1)2433454303
Pg. 236Faux-Gurt (opt. 2)2248244404
Pg. 250Vanilla Coffee Creamer2T777.20.60.900.9
Pg. 256Chedder Cheese Chips822818.5140.700.7
Pg. 257Crispy Pepperoni Chips6605.43000
Pg. 258BLT Boats826924.69.52.60.81.8
2 Replies
Solution

You need something like in the file attached.

 

You insert data in the DATA sheet (obv). Be careful to not switch columns.

 

Then, when you need to check numbers, use the CHECK sheet.

Select from the dropdown menu (that you need to update -data, validate cell, list, select the new interval) the dish you want, it shows you aside the values from DATA. You also need to update sheet CHECK if you add more receipes (to get all cells in the formula).

 

On the top of the page, it sums all the datas from the dishes you chose and alerts you if something is wrong.

 

Do not delete data from grey cells and do not erase rows even if you're seeing it blank.

Arul,

 

Thank you so much for the quick response. That is exactly what I am trying to do. This will make it easier to create my daily planning. Again thank you so much. ~Steve

Related Conversations
What are known folders ???????
stev Wolf in Office 365 on
6 Replies
EXCEL CHANGES DATA AFTER SCENARIO IS SHOWN
V. K. in Excel on
5 Replies