0

Okay, this is going to be a bit long, sorry!

In general terms, I'm looking to create a spreadsheet that adds up the total quantities of things from multiple different sheets in the same workbook, but only when those sheets are selected on a checked list.

In more specific terms, every year I make a huge amount of holiday cookies, and I want to be able to have all my recipes in one place, one per sheet, and then be able to select from a list which specific cookies I want to make this year, and have my spreadsheet total up the amount of each ingredient I need for those cookies in my Shopping List. I'm stuck on how to make my calculations only include specific recipes when the checkboxes are checked.

What I currently have:

  • Every recipe follows the same format, so it's easy to reference cells from sheet to sheet

  • My shopping list page has a spot for me to input the number of gift bags I'm making, and then that number is referenced by each recipe, and those sheets tell me the number of batches I need to make for each type of cookie to get the desired quantity of that cookie

  • On the page Shopping List, I have a box "Number of Gift Bags" (Shopping List Calculator!J3) and then each recipe has the Quantity per Batch ([Recipe Name]!F4) and # per bag ([Recipe Name]!G4) as manual input, and then on the recipe I have it calculating the Bags per Batch (H4=F4/G4) and then Batches Needed is calculated by

     =CEILING(('Shopping List Calculator'!J3/H4),1)

    So that way I get the number of batches needed as the next whole number rounded up.

  • I can make it so the shopping list has the total for each ingredient for all cookies at once by having

      ([Recipe Name]![Ingredient Quantity] * [Recipe Name]!Batches Needed) + ([Next Recipe]![Ingredient Quantity] * [Next Recipe]!Batches Needed), etc,

    but that's very cumbersome and I know there's probably a cleaner way to do this.

  • I'm also trying to make a Quick Chart page that lists each cookie name, the number of batches, the total amount of flour, sugar, eggs, and misc other ingredients. I can make it do that by manually inputting the cookie name and then having each specific ingredient listed out by

      ='[Recipe Name]'![Ingredient Quantity]*'[Recipe Name]'!Batches Needed

    and then having the Misc ingredients listed out by

      =TEXTJOIN(", ", TRUE, '[Recipe Name]'!A20:A49)

    where A20:A49 are the cells where misc ingredients would be listed, if any, but again, I think I might be doing things too manually and making them too complicated.

I think there's probably some CountIf function I can use in combination with checkboxes to get what I want.

On Shopping List!, I have a list called "Cookies Being Made This Year" that has check boxes in one column, and the name of the cookie in the next column.

What I think I need to do is have each cookie name be a reference to the specific recipe sheet, and then use the Checkboxes as a True value for a CountIf function, and then I'm stuck, and I feel like I'm missing something very obvious so I don't have to have a function within a function within a function here.

For a very abbreviated example of the data I'm working with:

Sheet: Shopping List

Number of Gift Bags
300
Ingredient Quantity Unit
Flour --- lbs
Sugar --- lbs
Eggs --- ea
Chocolate Chips --- oz
Ginger --- g
Cinnamon --- g
White Chocolate Chunks --- oz
Macadamia Nuts --- oz

Goal of Shopping List: To be able to calculate total quantity of each ingredient needed as pulled from recipe sheets, including miscellaneous ingredients which are different for every recipe, and standard ingredients (flour, sugar, eggs, oil, butter, etc.) I've abbreviated the list above to make it shorter, since the formulas will be the same for 3 ingredients and 30 ingredients. Ideally, there will be some way to have recipes included/excluded without needing to rewrite the formulas (I'm making this spreadsheet to be used by relatives who are even less tech-literate than myself.)

Sheet: Quick Chart

Cookie Name Flour Sugar Eggs Misc Batches needed
Chocolate Cookie 6 4 4 Chocolate Chips 4
Ginger Cookie 8 8 6 Ginger, Cinnamon 6
Sugar Cookie 6 8 8 4
White Choc Nut 3 2 4 white chocolate chunks, nuts 3

Goal of Quick Chart: Have the total quantity of each ingredient for each cookie listed, as well as which miscellaneous ingredients are used for that recipe (quantity doesn't need to be included), and the number of batches being made of that type of cookie. Real sheet currently only has Ginger Cookie data added to it.

Sheet: Chocolate Cookie

Recipe and quantity of each ingredient, as well as number of batches needed to make target amount of cookies.

Sheet: Ginger Cookie

Recipe and quantity of each ingredient, as well as number of batches needed to make target amount of cookies.

Sheet: Sugar Cookie

Recipe and quantity of each ingredient, as well as number of batches needed to make target amount of cookies.

Sheet: White Chocolate Nut Cookie

Recipe and quantity of each ingredient, as well as number of batches needed to make target amount of cookies.

Link to spreadsheet in Google Drive, though I am working in Excel. https://docs.google.com/spreadsheets/d/1oAB33p-wj1Yrtj6O8q_FAA7bYw-6u2R0tER2ncu4-xg/edit?usp=drivesdk

Improve this question
7
  • 1
    Please short your question. I assume you need REDUCE() function to iterate all sheets mentioned in list then do few operation with data of those mentioned sheets. You may share a sample google-sheet with dummy data. Commented Oct 30 at 3:39
  • 1
    Please edit your question and insert additional tables that show the structure of each sheet, together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Commented Oct 30 at 7:10
  • 1
    "Creating an Excel spreadsheet" — the question is tagged [excel] and [google-sheets]. The [google-sheets] tag description explicitly states "Do NOT use with [excel]" and the [excel] tag description explicitly states "Do NOT use with other spreadsheet software like [google-sheets]." Choose one platform and edit tags appropriately. Also remove the [reference] tag as it's not suitable for the question. Commented Oct 30 at 14:02
  • 1
    Your biggest issue is your data design. Having one recipe per worksheet makes things far more complex than they need to be. If you were to consolidate your recipes into one sheet with a column for "Cookie Name", you would be able to simply filter your data to pull up any recipe. Then you need a list of the cookies with a column for the amount on order. A lookup to pull the qty from the cookie table in your recipe table would give you the total demand by ingredient. With this, you would have a very straightforward process to produce your shopping list. Commented Oct 30 at 16:32
  • 1
    @untrainedsloth, you can store the instructions in the same table with the Cookie Name and qty to produce. I've done a very similar spreadsheet before (engineered doors, not cookies) and it's a pretty straightforward design. Basically, you have one table that has the information related to the Cookie (baking instructions, qty to produce, description, etc.) and another that has the ingredients for the Cookie. Commented Oct 31 at 13:38

1 Answer 1

Reset to default
2

Since you are already working with Excel. and that your question sounds more like a little project :)
I will give you some "cooking" instructions for how to do it yourself:

  1. Start with a database. meaning:

    1. An Excel sheet that includes all of the Recipes.
      It should have the following structure of columns:
      Cookie Name | Ingredient Type | Ingredient Name | Unit | Quantity | Original Quantity | Bags per Batch (can be calculated field, based on the next sheet)

    2. Another Excel sheet that will include Cooking:
      Cookie Name | Quantity per Batch | Units per Bag | Bake Temp | Bake Time | Scoop Size

    3. *Optional, another Excel sheet for the Cooking Instructions:
      Cookie Name | Step Number | Instructions

  2. "Create Table" (CTRL+T) out of each table above and give it the appropriate name - this will allow you to add columns to the same table in the future.

  3. "Create Pivot Table"s in order to produce the following:

    1. Cookie Sheets
      Use the filter to select the relevant Cookie Name
      Use the ingredient type, ingredient name, and Unit in the rows
      Use the Quantity, Original Quantity as values
      Add another Pivot Tables for the total quantities, baking order, and the instructions.
      Repeat this for each of the cookies

    2. Shoping List
      Create a single Pivot Table:
      Rows: Ingredient Type | Ingredient Name | Unit
      Insert Slicer for Cookie Name - so you can select the cookies you wants
      Values: Total Quantity* (see next)
      pick a cell (out of the pivot) and use it to "Enter Bags" (e.g. J3)
      Create a Calculated Field in the Pivot Table, name it "Total Quantity"
      Formula = Quantity * $J$3/ Bags per Batch

This will allow you to make any changes in the source tables and you just need one click to update the pivot tables. Even the interface will be nicer.

Good luck!
and let me know if you have any question

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.