Help with Macronutrient bank

Hey everyone,

I'm really new at excel usage (I'm sure you guys have heard that a thousand times) and I need some help with some work I'm doing.

I'm developing a macronutrient bank to streamline my meal planning process (sorry, I hate all the programs that do it for you).

I want a "bank" sheet that all the data is on, and then other sheets for either meal plans or recipes. I've uploaded a bank with some data. I've already figured out some of the basic formulas, but I can't figure out some things.

I want to be able to enter in the name of one item and the grams of what I've used and automatically the macronutrients generate on the meal plan's sheet. I know this requires some percentage work, but I don't know where to even start.

Any and all help provided is much appreciated. Any data requested will be given promptly as well as questions answered.

Thank you all for your time.

Cheers, Patch

I have created something similar myself. Mine only calculates calories, but the concepts are the same.

The first thing I would recommend, before looking at formulas, is to standardise your quantities as this will greatly simplify any calculations that need to be done. The quantities in your food bank include grams, ounces, and many other sizes.

All of the items in my spreadsheet show the number of calories per gram, or calories each. So where you have values per cup, scoop, piece, teaspoon, etc, I calculate the value each, and include the measure in the item's name. For example, you have values for two pieces of sushi. I'd name it sushi (piece), and halve all of the values so you now have the amount of protein per piece of sushi.

Similarly for anything that is by weight, first decide whether you want to use ounces or grams, and standardise everything to be the amount per ounce/gram.

Once you have that, you can complete your daily nutrition log with the number of ounces/grams, or number of units, and formulas can be used to perform the calculations.

Ok, I see what you mean.

As per standard, I can see the confusion in the first macronutrient bank I posted. I do plan on standardizing everything in grams. I'm having a lot more trouble as I continue.

First, would you mind if I could see your calorie spreadsheet?

Second, I'm having trouble developing the drop down menus to insert items into separate sheets. Here's a more basic format of what I'm trying to work with, might be easier to understand where I'm going with this.

Thanks for any more help provided!

Cheers,
Patch

Hi Patch,
I've been playing with your spreadsheet, so this is probably a better example than uploading mine.

Firstly, please consider removing all of the surplus blank rows and columns. You'll find it much easier to work with Excel if all of your data is in a table format with no gaps. You can still resize columns to space them the way you want.

Also, try to avoid merging cells as that will prevent some functionality from working. One example of this is dropdowns. I've added dropdowns to the Food column on the Mon tab. This is done by fist making a list of values you want available in the dropdown. Instead of making a separate list, I've used the list of items on the Food Bank tab. This was only possible after un-merging the cells in the column. The next step is to select the cells that you want to contain the dropdowns then, on the Data tab, click Data Validation. Set it to Allow: List, and in the Source, choose the list of items. Click OK and the dropdowns will appear.

On the Food Bank tab, the grey section contains formulas that calculate the unit quantities by dividing the original amount by the quantity (eg 6.0 grams of protein in .25 cups = 24 grams of protein per cup). I haven't done any calculations to convert oz to grams / fl oz to ml or vice versa, but I'd recommend doing this.

Then, I included VLOOKUP formulas in the Mon tab to look for the food's name in the Food Bank tab, find the amount of protein, etc, and multiply it by the Quantity.

This means you choose the food from the dropdown, enter the quantity that you're including in the meal and it will calculate the rest for you.

Awesome, thank you so much for the help! I greatly appreciate it!