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...

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.