Creating Alpha-numeric tables

Question

I am trying to create a table consisting of 9 columns (MAX).  Column A would contain text data, Column B is the time of the activity and the remaining columns varioius other info.  Whenever a 'text' value is inserted into column A I would like for the corresponding value to be applied in the appropiate column.  (ie, if column A is 'english muffin' then column d would be 2, no matter what row).Also is there a way to add a multiplier (ie 0.5 'english muffin and get 1 in column d.  Once the max is reached (say 5) I would like that column to turn green.  Column A is broken down in categories of breakfast, snack, lunch, snack, dinner, snack.  Any help would be appreciated, as my Dr has put me on a strict renal diet and between always having my computer and/or phone this would be an easy way for me to keep track.  PS - the list consists of about 500 foods but I will be inputting the info as I eat the food until the list contains the foods I am most likely to eat.

Answer 1

Date

Meal              Time     Meat/Protein(5)     Starch/Bread(6)    Fruit(6)   Veggies(3)    Milk(0.5)   Fat(5)    Wate(64)  

 Breakfast         8:00             

English muffin                                                   2

1 Tbs maragine                                                                                                                       1             

 Egg                                                                                                                        0.5

Snack

Orange                                                                                   1

Sample of partial day for yesterday.  The number () are the max for that day.  I can set auto-sum to tell me how many I've used and would like the column to turn green when I've reached the max.  From what I've read I will need to set the data up on another sheet, I'm planning set that table up by category (ie, meat, starch, etc)

Thanks for your help.

Answer 2

The answer is yes, this is definately possible.

However you have not really provided enough information for people to give you clear advise. Can you mock up the table you are interesed in, ie below. What is in column C-D-E etc.

	Column A	Column B	Column C	Column D	Column E	Column F
Header ?	Food	Time / Date	?	?	?	?
Data 1	English Muffin	09/10/2010	1
Data 2
etc

Also, you might find a usefull template on the microsoft site

http://office.microsoft.com/en-us/templates/results.aspx?qu=food#ai:TC030005030|

http://office.microsoft.com/en-us/templates/results.aspx?qu=food#pg:2|ai:TC030002019|

Answer 3

For first question, you're going to need to setup a table somewhere correlating all the names of your foods with their respective values. It sounds like you have this from your doctor or website, so shouldn't be too hard. You can then use a VLOOKUP function to populate columns D-I (see help file for details).

For the second question, I'd recommend using another column for "QTY" and then modify formula in column D to be something like:

=Qty_Column * VLOOKUP(Your_Formula)

As XL doesn't charge you for the amount of columns you use, this would be your best bet (easy to input as well). YOu could even start doing SUMs and FREQUENCY stat checks to see what you eat a lot of.