View Question
 Question
 Subject: Excel Spreadsheet Formula/Code Category: Computers > Programming Asked by: alisak-ga List Price: \$10.00 Posted: 31 Oct 2006 17:06 PST Expires: 30 Nov 2006 17:06 PST Question ID: 778977
 ```I would like to create a Microsoft Excel spreadsheet that will allow me to type in the name of a food, and in different cells in the same row, the number of carbs, proteins, calories and fat grams will pop up. I know I need to program the numbers in for each food item, but I need to know how to do that. Thank you!``` Clarification of Question by alisak-ga on 31 Oct 2006 17:24 PST ```As the answer, please prepare a spreadsheet with dummy numbers in it so I can duplicate the code and place the correct numbers. That is: In column A, if I write "salmon", then in column C there should pop up the amount of protein, then in column D there should pop up the amout of carbs, and in column E there should pop up the amount of fat. In column F there should pop up the number of calories. I'd like these numbers to automatically come up when I type in "salmon". Or any other food. Please create a template, and give me instructions on how to duplicate the code for every food I have on my lists. Thank you!``` Request for Question Clarification by sublime1-ga on 31 Oct 2006 17:27 PST ```alisak... I'm not sure I understand the value of having the information pop up. Since each unique food item would need to be programmed separately anyway, it would actually be much simpler just to enter the information for each one in adjacent cells with appropriate title headers. You would have to enter all the information in a formula anyway, and it would be much more complicated to do it that way.``` Clarification of Question by alisak-ga on 31 Oct 2006 17:58 PST ```I'm sorry, I should have mentioned how I'm using this. I am keeping a diet journal, and on each row I list a food that I eat. In each column, I list 1)the size of the portion, 2)the number of protein grams 3)the number of carb grams 4) fat grams 5)the number of calories. I take the actual values (carb grams, protein grams, etc) of each food from a list my trainer prepared for me. It's become very tedious to plug in the food I've eaten, the find on the other list the amount of protein, carbs, fat grams, and calories for each and every food - while I could hopefully have those numbers already programmed into Excel, so I won't have to keep looking them up every time I have a meal. So...if I spent a few hours plugging in the numbers into a formula, it would same me the time and trouble of looking them up after every meal (and to tell you the truth, I have put this off for the past 3 weeks and am in my second hour of trying to put together my log, looking up the protein, carb and fat values of each food I'v eaten in the past 3 weeks). Here's an example for one day last week, Meal #3: Food......Portion......Protein......Carbs......Fat......Calories Salmon....3 oz..........23..........0..........9.........173 Green beans..1/2 cup....1...........2..........0..........12 Wheat bread..1 slice....2...........11.........1..........61 I had to look up each food to plug in those numbers. Imagine doing that after every meal, or at the end of each day! I'm ready to give up. But I figured combined with Google's online spreadsheet, and some formula that would free me from having to look each item up every time I type it out, I'll be happy to do it. Thank you for responding sublime1. I hope I haven't scared you away...```
 Subject: Re: Excel Spreadsheet Formula/Code Answered By: maniac-ga on 02 Nov 2006 17:56 PST
 ```Hello Alisak, There are actually a few different methods that can be used for this kind of problem, but the solution described below is easy to implement and can be extended for more food items and is quick to use when entering your diary items. Let us assume that your journal will be on one sheet (say Sheet1) and the list of information from your trainer is on a second sheet (say Sheet2) (the food list, portion size, and other information). In an empty spreadsheet, I selected Sheet2 (at the bottom of the window), and entered the following information (from your example) in cells A1 through F4). This list can be extended for as many rows as the types of food from your trainer. Food Portion Protein Carbs Fat Calories Salmon 3 oz 23 0 9 173 Green beans 1/2 cup 1 2 0 12 Wheat bread 1 slice 2 11 1 61 I suggest you define a name for the cells that contain this information (in case you add / remove rows or move the list). To define a name, select the region (click / drag over the cells) and then use the menu Insert -> Name -> Define and then enter a name like Food to refer to that region in the formulas below as Food. You can include or exclude the header row if you like. I also suggest making the region a little larger than you currently have food items & insert rows somewhere in the middle of the region when adding new food items. NOTE: From my testing, the case (upper or lower) of the food items can vary. So a name like salmon, SALMON, and Salmon will all "match" equally. Now in Sheet1 (be sure to select Sheet1 at the bottom of the window), go ahead and enter the same column headers. [In the sample sheet I prepared, I did this in A1 through F1] In cell B2, enter the following formula: =VLOOKUP(\$A2,Food,2,FALSE) Let me explain what this formula will do. The VLOOKUP function is used to look up values in a region of cells. The first value (\$A2) refers to the cell in column A in absolute terms and row 2 in relative terms. That is the value that will be matched in the region. The second value (Food) refers to the region of data to look up. The first column has the values that are matched to determine the row that data will be extracted. Be sure to use the same name you entered when you named the region on the second sheet. The third value (2) refers to the column to extract the data. In this case, the second column (referring to the portion size) will be fetched. The fourth value (FALSE) is used to require an exact match. In this way, the information can be entered in the region in any order (if TRUE - the entries MUST be in alphabetical order). There is additional information in the on line help for Microsoft Excel - enter VLOOKUP as a search phrase to find that information (as well as similar functions). At this point, I suggest you select the cells B2 through F2 (click drag) and fill right using the menu Edit -> Fill -> Right All those values should show #N/A at this point (since you don't have a value in cell A2). Go ahead and change A2 to salmon (or another food item) and the entire row should show the portion size for that food item. Now, let's modify the cells in C2 through F2 to fetch the proper values. As identified above, the third value is the column to fetch so change C2 through F2 as follows =VLOOKUP(\$A2,Food,3,FALSE) =VLOOKUP(\$A2,Food,4,FALSE) =VLOOKUP(\$A2,Food,5,FALSE) =VLOOKUP(\$A2,Food,6,FALSE) If you have a proper food item in cell A2, the values in row 2 should display like the example you provided. Now you can select B2 through F50 (or as many rows you want) and use the menu Edit -> Fill -> Down and #N/A should appear on all rows where there is no food item entered. [let me know if these must be blank - I can suggest a slightly different formula to do that as well] As you enter each food item in column A, the values should be fetched properly from the "Food" region on Sheet2. If the food item is misspelled, the #N/A will still be shown. Also, if any part of the answer is unclear, you have any difficulty with the formulas, or you need additional enhancements to the solution (as mentioned above or otherwise), please make a clarification request. I would be glad to help further. Good luck with your training. --Maniac```
 ```There are sites that might make this easier for you. Try www.sparkpeople.com or www.fitday.com Both are free, have pre-existing food databases, and will let you enter in your own food information and save it for later. These sites will also analyze the vitamins in the food you eat, track your weight, and show you reports on how your food/weight vary over time.```
 ```Thank you, Stephanie. I've tried FitDay and even bought the software program. I like the program, but would rather have an internet-based one. Also, it was really for my trainer, who created the spreadsheet as a diet journal, but without the macro/formula that would make it easier to fill in. I've got to submit it to her every 2 weeks and it's too time consuming to just fill in.```
 ```alisak... No, you haven't scared me away, but I'm afraid the challenge exceeds my skills.```