Lists - autopopulating fields based on what's chosen from list

[obiwanvaldez]

I hope I am asking this question correctly. I have a predefined list of foods that I am using for a nutrition workbook. On my source list I have a food then a qty, calorie, fat, protein carbs cells that are matched to whatever food they are next too. I have a total of 8 worksheets - Monday-Sunday and then the Foods list worksheet. On my days of the week worksheets it has the same columns - food, qty, calorie, fats, protein, carbs. My question is this - I am using the list for the column on foods. If I pick a food from the predefined list, for example a banana, can I have the other fields besides qty autopopulate? So if I pick banana can I have it's calories fat protein and carbs autofill? If so, how? I appreciate everyone's help!! Hopefully it made sense. Thanks again!!

 

[VoG]

Try using VLOOKUP http://www.contextures.com/xlFunctions02.html

 

[obiwanvaldez]

That seems to do the trick. Thank you very much!

I have run into a couple issues with this that you might be able to help me with. First, I'd like to be able to take a food, for example a banana. Thanks to the Vlookup I now have all the calorie and other fields filled. If I want to change the quantity, let's say put 2 in quantity for bananas, can I have a formula that takes the populated field n multiplies it by the quantity and give me the new total? Also, I have N/A's in the fields that are waiting for the info to lookup? Can I make these be blank based on whether or not there is any info in the food column?

And last but not least, I had a sum formula totaling the calories of the food for the day. The lookup function makes that formula not work. Is there a way to fix this?

Thanks again for the help! I apologize if this sounds basic but I'm still rather novice with excel.

 

[VoG]

You should be able to multiply the looked-up value by quantity.

To avoid #N/A

=IF(ISNA(your formula),"",your formula)

 

[obiwanvaldez]

Ok I tried that formula and came up with this - =IF(ISNA(VLOOKUP(B4,'Favorite foods'!$A$2:$F$100,3,FALSE),””,VLOOKUP(B4,'Favorite foods'!$A$2:$F$100,3,FALSE). However, it's not working. I think I did something wrong I'm just not sure where.

Also I tried multiplying by I'm not sure why it's not working for qty. Where would I put the * sign?

And is there a way to use a SUM formula to add up the calories that have been autopopulated?

Thank you for all your assistance!! I appreciate it!

 

[T. Valko]

Ok I tried that formula and came up with this - =IF(ISNA(VLOOKUP(B4,'Favorite foods'!$A$2:$F$100,3,FALSE),””,VLOOKUP(B4,'Favorite foods'!$A$2:$F$100,3,FALSE). However, it's not working. I think I did something wrong I'm just not sure where.

Also I tried multiplying by I'm not sure why it's not working for qty. Where would I put the * sign?

And is there a way to use a SUM formula to add up the calories that have been autopopulated?

Thank you for all your assistance!! I appreciate it!

You're missing some closing parentheses.

Try it like this...

=IF(ISNA(VLOOKUP(B4,'Favorite foods'!$A$2:$F$100,3,0)),””,VLOOKUP(B4,'Favorite foods'!$A$2:$F$100,3,0))

If you're using Excel 2007 or later you can use this version:

=IFERROR(VLOOKUP(B4,'Favorite foods'!$A$2:$F$100,3,0),"")

 

[obiwanvaldez]

Ok I tried that and it didn't work either. I don't know what I'm dping wrong. Thanks for the input on this tho.

 

[T. Valko]

Ok I tried that and it didn't work either. I don't know what I'm dping wrong. Thanks for the input on this tho.

Are you getting an error? If so, what does it say?

 

[obiwanvaldez]

Yes I'm getting an error and it shows the two "" marks and also the b4 cell as being the problem

 

[T. Valko]

Yes I'm getting an error and it shows the two "" marks and also the b4 cell as being the problem

I thought those double quotes looked different.

The IFERROR formula I posted is using the correct double quotes.

Here's the ISNA version with the correct double quotes.

=IF(ISNA(VLOOKUP(B4,'Favorite foods'!$A$2:$F$100,3,0)),"",VLOOKUP(B4,'Favorite foods'!$A$2:$F$100,3,0))

I have no idea what Excel might be complaining about with regards to the cell reference of B4. The formula is syntactically correct. Let's see what happens with those different quotes.