Hello i want to create a food database as follows:
But in the Food Qty cell i must be able to select another value ex. 230 g and the calories and all other nutritional info would change. How can i do that? I just started learning Excel 2013.
Food Name Food Qty Calories Protein Carbs Fats Milk 0.1% Fat 100 g 31 3.2 4.5 0.1
Thank you.
The simplest method is to use two sheets or two areas within the same sheet.
I will create a sample for you.
I changed the cell where you entered 100g
Entering numbers like that complicates everything.
I entered 100 and created a custom format to show the g.
right click on the cell
select format cell
select custom
and enter 0.00 "g"
Last edited by mehmetcik; 08-27-2014 at 03:24 PM. Reason: Wrong Sheet Attached
This is a little different approach. The original data is on the DATA worksheet and on the MAIN worksheet you fill in the amount that you want and the rest is filled in.
The basic formula used is:
Formula:
=IF(A2="","",VLOOKUP($A2,Data!$A$2:$F$10,COLUMNS($A$1:C1),0)*$B2/Data!$B2)
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Yes, Thank you very much newdoverman that is exactly what i needed. Now i must figure it out how to replicate it and create it myself.Originally Posted by newdoverman
This is a little different approach. The original data is on the DATA worksheet and on the MAIN worksheet you fill in the amount that you want and the rest is filled in.
The basic formula used is:
Formula:=IF(A2="","",VLOOKUP($A2,Data!$A$2:$F$10,COLUMNS($A$1:C1),0)*$B2/Data!$B2)
Thank you i really appreciate it but now there are 2 Food Qty Cells. and when i change the value in the first one, it changes only the second Food Qty cell.
For example i change the Food Qty cell to 90 g, It must become like this:
Food Name Food Qty Calories Protein Carbs Carbs Milk 0.1% Fat 90 g 27.9 (31 * 90 / 100) 2.88 (3.2 * 90 / 100) 4.05 (4.5 * 90 / 100) 0.09 (0.1 * 90 /100)
I made a slight mistake in the basic formula. It should have $A2 at the start like this:
Formula:=IF($A2="","",VLOOKUP($A2,Data!$A$2:$F$10,COLUMNS($A$1:C1),0)*$B2/Data!$B2)
The formula is entered in C2 on the MAIN worksheet and copied across and down as far as you want. The range for Data!$a$2:$f$10 has to be extended to match the length of the data on the DATA worksheet.
It isn't a complicated formula. It is a basic VLOOKUP and uses the COLUMNS function to determine which column from which to return values.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
xellero 
Register To Reply
Bookmarks