Ideas on creating spreadsheet for tracking nutrients in smoothies

Continuing…

At this point, "Ingredients" should have a list of ingredients in column A and a list of the corresponding amount of each property in the columns to the right.

"Design" should have the same list of properties in the same order across the first row, and a list of the possible ingrediente down column A. All other cells in Design should be empty.

The next step is to enter a formula that will gather the properties of the ingredient named and put them in the correct row of "Design"

For the data layout suggested, VLOOKUP is a suitable formula.

In cell B2 of Design, enter this formula:

IF(ISBLANK($A2),"",VLOOKUP($A2,Ingredients::$A:$E,COLUMN(),FALSE))

"FALSE" will change in the formula to a lozenge labelled "Exact Match" (as seen in the image)

On clicking the green checkmark button, you should see the value from B2 of Ingredients copied into B2 of Design.

Fill that formula Right to the last column of row 2, and down to the last row of the table, and you should see an exact copy of the content of Ingredients in the same cells of Design.

(This version of the formula is missing the IF part that keeps the calculation in each cell from occurring until an ingredient has been chosen in column A of that row) See above for the full formula.

Note that the example table shows an 'exact copy' except for one cell—D5 of Ingredients is empty, but D5 of Design contains 0.

This is the default value returned for an empty cell, and is fitting here, as it returns the same total as would an empty cell.

TOTALS

For your design table, you will want a Totals row to calculate and total the amounts of each property item in the smoothie design being constructed.

Select any cell in Design to activate the table and show the column and row reference tabs.

Then place the pointer between the tab for the bottom row of the table and the table itself, and click the v that appears there to open a short menu.

In the menu, choose Add Footer row.

In cell B11 (in the Footer row) type = to open the formula editor and enter this formula:

SUM(B)

And click the green checkmark button to confirm the formula and close the editor. Then use the pointer to drag the fill control (small yellow circle) right to fill the formula into the rest of the cells in the Footer row.

Clearing the Design table:

Select cell A2 (the first pop-up menu cell), then click the control that appears to the right edge of the cell and set the menu to None.

Then either: Repeat with the rest of the pop-up menu cells to set all to 'none',

OR

Grab the Fill control of cell A2 and Fill down to the rest of the body rows of the table, leaving this appearance:

Choosing any of the ingredients from a menu in column A will bring that ingredients numbers to the design table, and automatically include them in the totals at the bottom of each column.

Regards,

Barry

"I am a novice spreadsheet user but I think I need to use Tables?"

That's pretty much a given with spreadsheet documents. :)

"Each ingredient would have its properties, protein, sodium, fat, calories per unit, vitamins, minerals, etc."

The first thing you need is a table. I would start with one that included one header row and one header column.

In the header row label each column with the type of data that column will contain: "Ingredient", "protein", "sodium", "vitamin name" (one column for each vitamin you are tracking), "Mineral" (again, one column for each if you are tracking them individually), and additional columns for any other property you want to track.

List all ingredients you will (or might) use in column A (a Header column), starting in row 2.

Before entering any data beyond those labels, make a duplicate of this table, and set it aside.

Select one of the tables, then click the format brush and choose Table from the boxes at the top of the Inspector panel.

Click the checkbox beside Table Name to display the name above the table.

Double click the name to select it, and change the name to "Ingredients"

Repeat with the second table, naming it "Design"

In each row of Ingredients, enter the amount of protein per unit for that ingredient in column B, the amount of sodium per unit of that ingredient in column C, etc. enter 0 (zero) in cells where that is the correct value for that ingredient and that tiem.

When you have completed filling "Ingredients:, turn to the Design table.

Select all cells in column A except the top one, containing the label "Item".

With those cells selected, choose Cell in the Format inspector and set the data format to Pop-up Menu.

Set the 'Start with' control to "Start with blank"

Caution: There may be a limit to the number of items you can put on a pop-up menu list. If you exceed that limit, let me know how many it is, and how many different ingredients you have, and we'll look at alternatives.

More to come later tonight, but I have to deal with other things for the next while.

Regards,

Barry