View original (French)

Shopping list from recipes

ChrisN77 -  
Hello,
I want to create a table (grocery list) from saved recipes, let me explain:
I have several recipes with ingredients and quantities in a table, examples:

Stir-fried Noodles
Chicken Breast 2 units
Noodles 160 grams

and
Gratin
Chicken Breast 3 units
Pasta 40 grams

etc....

My goal is to select one or more recipes from a list, and based on the ingredients, have a table automatically created with the list of ingredients and the necessary quantities. (my grocery list)

In the above case,
if I select the 2 recipes,
a table will be created with:

Chicken breast 5 units
Noodles 160 grams
Pasta 40 grams

I hope I have been clear enough.
If anyone can tell me how to do this…..thank you!

Christophe
Excel 2013

Configuration: Windows / Internet Explorer 11.0

10 answers

  1.  via55
    Hello

    Several ways to proceed, through formulas, through macros, it all depends on the structure of your file
    Post an example of your recipe file on cjoint.com, create a link that you copy and come back to paste here, we will assess based on that

    Best regards
    Via

    --
    “Imagination is more important than knowledge.” A. Einstein
    1
    1. via55
      Normally everything should update, it works for me
      Check that you are set to automatic calculation and not manual (see in Formulas - Calculation options)

      Creating a new sheet where you copy the list of ingredients and then hiding the unnecessary rows is tedious too, so I created a macro to automate this that runs by the button in the Ingredients list sheet, see if that works for you
      https://mon-partage.fr/f/dxLkoHsW/

      --
      "Imagination is more important than knowledge."    A. Einstein
      1
      1. ChrisN77
        Attached is the link with the file:

        https://www.cjoint.com/c/IBdunqHFU0R

        I would also like the other sheets' lists to be updated whenever I change the ingredient list.

        Thank you in advance.
        0
        1. via55
          What do you mean when I modify the list of ingredients ... ?
          That if you correct the name of an ingredient, it also changes in the recipes?
          Or that if you replace one ingredient with another, it replaces it in the recipes? This last case is more delicate!
          Give me specific examples

          --
          "Imagination is more important than knowledge." A. Einstein
          0
          2. ChrisN77
            Basically, if I add an ingredient to my first table, it automatically adds to the other tables in the other sheets.
            But I can always do it manually if needed.

            The most important thing is that I can have a shopping list based on the recipes I want to make.
            Right now, I've managed to get the total of all the ingredients from all the recipes, but that's not what I want.
            0
            1. via55
              Hello

              Your file in return
              https://mon-partage.fr/f/SPvWexTH/

              I added a column with formulas in the Ingredients List to list the names of all the sheets in the workbook. Do not touch this column
              This list feeds the titles of the table I added in Shopping List
              This table automatically updates if you add a sheet with a recipe or if you add an ingredient to the list

              Then, to get the shopping list, you first put an X under each recipe name you want to make, and your ingredients list table updates
              On this table, I added a third column to filter; once you've put the Xs, you filter keeping only the - and you have your ingredients list

              Check if this works for you

              Best regards
              Via

              --
              "Imagination is more important than knowledge." A. Einstein
              0
              1. ChrisN77
                Hello,

                the result is exactly what I wanted. THANK YOU
                I added some recipes and everything is working perfectly.
                Well, I haven't understood all the formulas yet, but I'm going to tackle that.

                Is it possible to create a dropdown list to select the desired recipe and have the list of ingredients directly linked with it? Instead of having a table with all the recipes?

                Anyway, thank you, as this has helped me a lot.

                Chris
                0
                1. via55
                  Re

                  With a dropdown list, you can only select one recipe
                  Here you want to be able to select several at once,
                  But how many? We can do it with several dropdown lists one below the other and report the results as X in the table I created and hide the table

                  What do you think?

                  A trial
                  https://mon-partage.fr/f/dpuWnUgY/

                  Be careful, the sheets Lists Ingredients and Shopping Lists must remain the 1st and 2nd sheets of the workbook, with the recipes coming after, so that the formulas that establish the list of tabs and reflect them in the shopping list work

                  0
                  1. ChrisN77
                    Perfect, that's exactly what I was looking for.

                    THANK YOU

                    However, I have a little problem: when I make a modification (adding a sheet, selecting recipes, etc...), I must manually calculate (F9) otherwise nothing is taken into account. I don’t understand.
                    Any tips?
                    Sorry if I'm asking too much, but I like to understand.

                    Chris
                    0
                    1. ChrisN77
                      Well, that's the best of the best.....
                      Thank you for your work!!!!

                      Chris
                      0