Spreadsheet help

[NotAGolf]

Evening all.

I'm making a spreadsheet that will easily help me track all the food I'm eating, e.g. calories/protetin/carbs etc.

I've got 2 sheets. The first contains a big list of all types of food that I eat. Each row contains 5 fields; name, amount, calories, protein, carbs, fat.
e.g Chicken, 100g, 97, 22.0, 0.0, 1.1

The second sheet will essentially be a list of all types of meals I eat. For example, Chille Con Carne will have a few food items under it including; mince, tomatoes, kidney beans, rice etc.

What I want to do is create a drop-down with all the foods on the first sheet listed, then it will input this into the second sheet, along with the nutrional data associated with that food.

How would I go about doing this in excel 2007?

Thanks for any help

 

[NotAGolf]

Ok, so I've made some progress. I've worked out how to create a drop-down box in the second sheet to pull in data from the first sheet. I've then used the following code:

=VLOOKUP(A37,Meals!A2:F100,2,FALSE)

Which will take a value of the first item of corresponding nutritional data from the first sheet. I can then drag this across the next 4 columns to bring in the other 4 pieces of nutritional data.

What I want to do now is instead of dragging it across manually, run a function of some sort that as soon as something in the combo box is selected, it will run the Vlookup code for all 5 corresponding values automaticlly.

I've no experience of using excel so not sure how to go about this. So far I've come up with part of a formula for the drop down box:

=IF(ISTEXT(A43)),.......then run Vlookup function in cells, x,y,z....

So I'm hoping to use the istext() function to test if there's text in the combo box, and if there is, run the vlookup functions. I don't know how to code this though. Any pointers?

Thanks.

 

[whitecrook]

sounds like a 'recipe' for disaster. You want to use a database for this.

I typed out a reply but then I thought I would actually do it instead. here it is: a basic 10 minute database

add stuff to the MEALS table and the INGREDIENTS table, then run the query to get the results. You would be better spending your time now, making some nice forms with drop downs etc, rather than trying to work around excel and using IFTEXT functions.....


http://whitecrook.ath.cx/db.zip