# Help populating cells with formulas based on contents of another cell

#### Mr_Intuition

##### New Member
Hi all. This is my first post and I'm hoping some of you excel gurus out there can help me. I have a spreadsheet I have made up that allows me to build a weekly menu and helps aid the user in counting calories. The first worksheet has the meal for Monday. Once I get the formulas entered, I'll just copy for the rest of the days of the week. Under "breakfast" on Monday, a user can use a drop down menu to pick a food item that comes from a list on another worksheet in the same workbook. Once they choose a food item, I want it to populate specific cells with corresponding multipliers. The user then enters the amount of the serving and that is multiplied by the multipliers mentioned above and thus populates one cell with number of calories, one cell with grams of fat, one cell with grams of protein and one cell with grams of carbohydrates. Hopefully this makes sense.

Below is a snapshot of the monday worksheet:

So when someone chooses bacon from the drop down list like shown above, I want it to lookup the nutritional information on this page:
and apply those multipliers/value to the cells on the Monday worksheet under "calorie", "fat", "carb", etc. That value will then be multiplied by the number in the "serving" column and an actual value will be placed in the row so the user knows how many calories, fat, etc will be consumed in a serving. The totals for the meal will be at the bottom (I know how to do that formula) letting the user know if the meal is in line with the diet he/she is on.

Hopefully this makes sense and isn't too confusing. I talked with one person and they said it would require some visual basic commands...which I know nothing about. Help please someone! I'm hoping this isn't too hard of a thing I'm trying to do!

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

##### Well-known Member
You don't need VBA here, a vlookup formula can return the multiplier.

#### Mr_Intuition

##### New Member
Thanks Sheetspread. Can you give me an example of how that formula should be constructed?

#### Mr_Intuition

##### New Member
I try the vlookup function and it keeps returning a different value than what I intended. For instance, when I do this with the Bacon example, it returns a value that is for C column (3) instead of the B column which is 43. Any idea why? here is the formula I have come up with so far: =VLOOKUP(B4,Food!1:65536,Food!B:B,FALSE)

##### Well-known Member
Your lookup range is too large and the second comma should be followed by a number or formula whose result is a number:

try: =vlookup(\$B4,Food!\$A\$2:\$E\$32,column(b1),0)

Last edited:

Replies
10
Views
1K
Replies
4
Views
187
Replies
3
Views
296
Replies
7
Views
782
Replies
0
Views
400

1,172,137
Messages
5,879,261
Members
433,414
Latest member
mwtharp

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back