Excel Help Please?

Colbaker · 9 May 2011 at 12:56

Hi all,

I am trying to create an Excel Spreadsheet to track Calorie intake, I can create a drop box to choose the food i have eaten (from a data set on a seperate page) but i want the next cell to populate with the Calorie info (also on the data from a seperate page)

How could i do this?

Thanks guys

dazzerd · 9 May 2011 at 13:05

vlookup us what you need (or index/match if you want to be fancy) assuming the calorie intake is adjacent to the food name.

So something like:

=vlookup(A2 (this is the cell you are changing),data!A:B (this is your data sheet ref and columns),2 (this is how many columsn you want to look up),0)

ta

Colbaker · 9 May 2011 at 15:18

Thanks for the reply, I have tried what i understood of your Vlookup equation but cannot get it to work, The below is a mockup of what i am going to do on my spreadsheet, as you can see i have the DATA on the right (this will only be used to populate the data on the left), I have drop down boxes on the left under the Food column and next to it i have a column for Calories, What would be the exact Equation to enter the numeric value next to the food type in the data section when selecting it relevent food type in the drop down box?

Many thanks

dazzerd · 9 May 2011 at 15:52

I'll have a look when I get home work blocks pictures

Royal Fleet · 9 May 2011 at 15:58

Formula in d4

=vlookup(c4,$f$4:$g$10,2,false)

Should work (the $ signs mean you can C&P down the column

Colbaker · 9 May 2011 at 19:59

Royal Fleet said:
Formula in d4

=vlookup(c4,$f$4:$g$10,2,false)

Should work (the $ signs mean you can C&P down the column

Thanks muchly that works nicely, also helps me to understand it a bit more, only issue i have is that the cells which has the equation in but there is no data entered in the drop down box has #N/A in it, is there a way to make it blank?

The reason i ask is that i have added a sum at the bottom adding al of the number populated in the new cells but becuase of the #N/A in some cells the sum also has #N/A

Royal Fleet · 9 May 2011 at 20:56

You would need to do something like

=if(c5="","",vlookup(c5,$f$4:$g$10,2,false))

This is in cell d5, and should work all the way down.

What is it basically saying is if the cell to the left (column c) is blank, leave this cell blank, otherwise do the look up.