Creating an automated set of lists

Question

I'm trying to create an automated shopping list. Easily select and input an available meal (from a list of options) into a planner and easily add any additional items to create a shopping list sorted by area and including the planned meals.

**My biggest issue right now is that I need a way to search a column in an expanding table for all values matching a reference cell and return just those rows into the next available blank row in a new table**

I've included a sample of all the tables. Halp?

Answer 1

In this situation, just looking at a screen capture it is hard to make suggestions.  I do have a few points I can make. But for best suggestions it would be better if you upload this example file for us to "play with" (without each having to recreate it).  3 sections below:

1. Suggestions from screen cap.
2. links to recipe specific articles I've collected
3. instructions for upload and sharing your example file

1 Suggestions from screen cap

What is the "additional table".  Is that non-recipe shopping list items?

"Options list" - is that the list of meals that can be picked in the "Meal Planner" table?

"Meal Planner" I would make this an Excel table with just 2 3 columns Day of week, meal, side.  Meals would be a drop down list based on Options List.

Will there be an equivalent of "Options List" for  meal "Side" selections?

Why do you have both "unique ID" and "Meal Options" in the Ingredients table?

What is the Long.Table? Is it just a collection of ingredients based on "meal planner" table for the whole week?

What is Day.Table.Monday? Is that a list of ingredients just for Monday?  Why are there 14 day tables?

I'm guessing you that in the "Finished" section, you want to generate the

• Meal Planner summary
• ingredients totals for: Fresh items, Shelf items,  Spices, and "cold" items

One thing to consider is "quantities" in the various places.  You are going to want to use consistent quantities, ie you don't want to say 1tsp salt in one place and 1tbsp salt in another. Without help Excel won't know how to add them correctly.  

What is a pkg of Toilet paper?  6 / 12 / 24 / 48 / 96 roll package?

I'm thinking pivot tables (if I can make them work) to reduce "manual copying"

Use Excel Tables for inputs. And therefore you can use "structured references", ie column names when referring to them.

2 Recipe Articles

Recipe- **Converting a Grid into Clean Segmented Lists**- Unpivot           2016 03 10

https://ozdusoleil.com/2016/03/10/converting-a-grid-into-clean-segmented-lists/  (7min)

LSatyreD asked about converting a massive grid of ingredients into their corresponding recipes.

In this video, I show how to do this without writing code!

In Excel 2016, Get & Transform and a pivot table will get the desired result.

.

Meal Planner -This next series of articles is from a single site showing the evolution of their Meal Planner workbook. Start with the first one in this list if you want to see the final evolution. Look at the other articles to see if they may have features or functions you want to use

Weekly meal planner – shopping list             2018 07 24

https://www.contextures.com/excel-weekly-meal-planner.html

To help you make the job of meal planning easier, you can use this Excel weekly meal planner template. Enter your favourite meals, list their basic ingredients, and click a button to create a shopping list for a week's meals. To see a quick overview of how the Excel Weekly Meal Planner works, watch this short video.

.

**Weekly Meal Planner**                 **2015 06 18******http://blog.contextures.com/archives/2015/06/18/excel-weekly-meal-planner-20150618/

There is an Excel weekly meal planner on my website, and you can use it to organize your meals, and create a shopping list. I hadn't used it for quite a while, and after opening it this week, I decided it was time for an update.

In the old version, the meals were entered in a long list, and you could choose up to 3 items for each meal.

The system worked, but it wasn't ideal. In the meal entry sheet, there wasn't even a spot to indicate which meal you were planning, if there were 2 or more meals on the same day.

In the new version, the layout is set up for 3 meals, and snacks, with a separate column for each weekday.

Within each meal block, there are 5 cells with drop down lists, so you have more room to enter meal items.

.

**Recipe Nutrients Calculator****** 2012 04 13

https://contexturesblog.com/archives/2012/03/13/excel-recipe-nutrients-calculator/

A couple of years ago, I posted an Excel Calorie Counter workbook, which has been very popular. In one of the comments, Autumn suggested that I add a recipe calculator to the file.

And in the latest version, that Recipe Calculator has been added. Thanks Autumn, for the suggestion and the reminder.

The new version also has protein, carbohydrates, fat and fibre data. If you download the workbook, please let me know what you think of the changes.

.

**Weekly Meal Planner Update****** 2011 02 21

https://contexturesblog.com/archives/2011/02/21/excel-weekly-meal-planner-update/

In December, I added an online recipe selector, created by Jimmy Peña, and described the new feature in a blog post.

This weekend, Alyssa pointed out a problem -- if you select a meal item twice, it's only added to the shopping list once.

That could cause problems, if you run out of food on Friday, and have hungry and cranky children waiting for their dinner. Thanks Alyssa!

.

**Weekly Meal Planner With Recipe Selector****** 2010 12 06

https://contexturesblog.com/archives/2010/12/06/excel-weekly-meal-planner-with-recipe-selector/

JP from Code for Excel and Outlook, sent me a "geranium" -- a copy of my Excel Christmas Planner, with a fancy new Excel Recipe Selector worksheet. JP's workbook has code that looks up recipes via web API, and returns the result to Excel.

For example, if you're tired of steamed carrots, you can enter "Carrots" in the Recipes worksheet, and find more exotic recipes online.

.

.

**CONVERT Functions**      2011 09 21

https://www.myonlinetraininghub.com/excel-convert-functions

With a family of 4 fussy eaters to feed (well actually, 3 fussy eaters and myself) I do a lot of research on the internet for new recipes. The problem is I often find great recipes but they’re in imperial measurements and I have to convert them. Actually, it’s not that big a problem ‘cause I can use the Excel CONVERT function.

.

There are another series of articles for cooking timing and seating planning. And yet a 3rd series of articles for Weight loss tracking ... and a "calorie counter" series <g>

Instantly convert various measurement units in Excel     2011 07 20****http://web.archive.org/web/20150414215913/https://www.ablebits.com/office-addins-blog/2011/07/20/convert-measurement-units-excel/

It seems impossible to list all measurement units used all over the world: second, kilometer, gram, hectare, work, laziness :-)… And what if you often deal with Excel tables that contain measurement units? Suppose, you have a big worksheet with product description columns. Let it be mass and volume.  You used to put the mass in kilograms and the volume in liters. But it changed and now you have to show it in grams and milliliters and need a completely new table.  Changing the data can take a lot of time if there are many different values. But it can be pretty simple if you have Measurement Units Converter for Excel which converts hours of tedious work into plenty of free time.

.

3 Upload & Share instructions

****************** UPLOAD EXAMPLE - TROUBLE SHOOTING - SHARE PERSONAL ONEDRIVE FILE (NOT BUSINESS ONEDRIVE)

.

Trouble shooting problems in files can be like a visit to the dentist, a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we can come up with a / “the” solution.

.

Often it is faster and easier for everyone if we have a “sample file” get "hands on", to look at, and to “play with”.

.

This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:

.

https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471

The article includes links to macros to randomize text in Word and numbers in Excel to preserve privacy

**************************************

.

This article describes another way of sharing Office files:

2020 03 09- Share a cloud stored document from Microsoft Office

https://office-watch.com/2020/share-cloud-stored-document-microsoft-office/

Using “Invites” to specific people from inside Office apps. Modify document access permissions and add a “note” with “how to” instructions for recipient

Share Tab of Backstage View in PowerPoint 365 for Windows   2020 10 12

https://www.indezine.com/products/powerpoint/learn/interface/365/share.html

Learn about the Share tab of Backstage view in PowerPoint 365 for Windows. You can share via OneDrive, PDF, or as an email attachment.

Applies to other Office 365 applications.

.

Answer 2

automated meal list_shortened.xlsx (meal workbook)? Hopefully one of those will work

1.     Response from screen cap:

ADDITIONAL.TABLE is indeed for loose, non-recipe-based.

OPTIONS.LIST is the list of meals that can be picked and entered into MEAL.PLANNER.

I don’t know how to do drop down menus yet. Could you instruct me on how to do that?

The OPTIONS.LIST currently has both meals and sides in it,

The unique ID was needed because when I didn’t have it, I would only get the first result and none of the following results. This would probably be fixed if I could solve that main issue (a way to search a column in an expanding table for all values matching a reference cell and return just those rows into the next available blank row in a new table).

LONG.TABLE is indeed just as collection of all the ingredients needed for the week. I was hoping it would help me with my formulas, but it’s looking a bit extraneous now.

DAY.TABLE.MONDAY is just the ingredients needed for the main meal for Monday. There are 14 tables because each day of the week gets a table for the main dish and a table for the side dish.

Ooh, that’s a good point about quantities. I hadn’t thought of that. As for toilet paper, the quantity doesn’t really matter. It is just a reminder. But I guess, it could all boil down to me not knowing how many rolls are in the package we normally get. >.>

I can’t visualize the pivot table thing in my head. I would need to see an example.

2.       Videos: The videos involve way more complicated/involved functions than I really want. I need the workbook to be easy (and enjoyable) to use. I prefer to manually type in new meal ingredients, I think. The idea of that huge grid table (from the first video link provided in the reply) seems really unpleasant to use. 

The workbook in the other several videos is way too involved and scary/distressing. I need things to be clean and simple or we will get overwhelmed. (I don’t mind making the back-end super complicated as long as it makes the user interface simple).

I’ll look into the CONVERT function when I get further along and get the workbook functional.

Answer 3

When you see a problem, ie a spreadsheet, that is big and "scary", take a step back and break it down into smaller pieces.

While the examples may come across as big and scary, but they are intended to make life simpler.  It takes some effort to set them up, but once you do that they are intended to be easier to use.

Using Excel Tables, and drop down lists based on them, you allow users to pick only valid choices. The pivottable is used to summarize things, like creating your daily and ingredients lists from the names of the main and sides you select.

So, the way I see this could work is

• Using tables simplifies your formulas because all you have to do is add new items to the bottom of the table for those items to be picked up anywhere else the table is referenced, like drop down lists.
• create your Options (main) and Sides tables as sources for your menu picks. Entries are unique in these 2 lists, ie

Salad 1

Salad 2

or

Salad Caesar,

Salad coleslaw.

• create your ingredients list, use the names in the Options and Sides tables to group the ingredients used in each recipe.
• Create your meal planner using drop downs from the meals and side tables.
• We then create the required pivot tables.

To use the whole working model will be simple enough once you do the initial data setup:

1. define your meal names in the meal table
2. define the side names in the sides table
3. define the items required in the recipe for each meal/side

Once that initial data setup is done 4. Using the weekly meal planner, pick the daily meal and side options from the drop down lists 5. optionally add items to the separate shopping list of non-recipe items 6. "Refresh" the pivot table(s)

If you upload your current file, so I don't have to waste time retyping everything, I can try to put together an example of what can be done as a starting point.   Use the instructions I provided in my previous reply to upload your file to your personal OneDrive and generate a "Share" link to it that you can post back here for me to use.

Here are some articles on creating "drop down" lists

@ Create a Dynamic Drop Down List in Excel – 2 wayshttps://excelchamps.com/blog/dynamic-drop-down-list/****A drop down list is a powerful tool. It can help you to make your data entry simple and quick. The more you add data he more frequently you need to update it. For this, the best solution is to use a dynamic drop down list in which you don’t need to update data source again and again. When you added a new entry into the source list, that entry is automatically updated in the dynamic drop down list. ET MR Validation.docx .

@ Add New Items to a Drop-down List Automatically – Dynamic Data Validation

https://www.youtube.com/watch?v=RLLdY37YUgs&feature=youtu.be

In this video I explain how to include new items in Excel drop-down lists automatically. These are referred to as Dynamic Data Validation Lists because they automatically extend to include new rows/cells added to the source data range...  *  Explicitly manually name the table validation list to make it easier to refer to in the drop down

Sort Drop Down Lists Automatically in Excel

https://www.excelcampus.com/tips/sort-drop-down-lists-automatically/

Learn 4 ways to automatically sort a data validation or drop-down list:

.  *  a VBA Macro

.  *  the List Search Add-in

.  *  Using Power Query

.  *  Using Dynamic Array Functions

.

Search Data Validation Drop-down Lists in Excel – AddonJon Acampora****https://www.excelcampus.com/vba/search-data-validation-drop-down-lists/

Learn a fast and easy way to search any data validation list or in-cell drop-down list with a free tool.

There is no built-in way to search the validation list in Excel.  It can be difficult to scroll through these lists when the drop-down contains a lot of items.  There are some really cool formula based solutions to this problem, but they require a lot of setup work for each validation list in your file.

.

Here are a couple of articles on creating PivotTables to get you started

Note: the pivot tables you will need are not simple Pivots based on a single table, we will have to "join" 2 or more tables to create the pivots you need. Don't worry about that for now. That is a "scary" part we can put off for now. We can address it if you decide to go ahead. It is "scary", but not really that hard to do, once you know how (are shown how).

.

!  Excel PivotTables Made Easy - And Why Things Go Wrong!         2020 09 09 Mynda Treacyhttps://www.myonlinetraininghub.com/excel-pivot-table-video-tutorial

https://www.youtube.com/watch?v=vQlFiLUaw4k&rel=0  13min17

Excel PivotTables are an awesome productivity booster, but many people find them difficult and scary. In this video I'm going to show you how easy they are and the common mistakes that give them their 'scary' moniker. May you never be scared of PivotTables again!

00:00 – Intro

00:00 – sample data

00:52 – Attributes of data being input to a PivotTable

01:38 – Define input data as Excel Table

02:00 – insert first PivotTable: Recommended table

02:50 – Insert PivotTable using ribbon

03:29 – Describe the functions of the “PivotTable Fields” pane

03:39 – Create simple “sumif” type pivottable

04:17 – Add columns

04:35 –autogeneration of a months group on date (auto 2016 and newer)

04:45 – Manually grouping/ungrouping fields

05:18 – Change function used on numeric values from sum using “Summarize Values by” or Value Field Settings”

05:53 – Refresh PivotTable

07:22 – Advantange of PivotTables: Speed and no formula errors

07:36 – Why people find PivotTables difficult

07:41 – #1: Wrong data layout, Pivotted, totals

10:25 – #2: Semi report format data

12:27 – conclusion

.

! (Intro) Create a Pivot Table in Excelhttps://www.contextures.com/CreatePivotTable.html

Follow these easy steps to create an Excel pivot table, so you can quickly summarize Excel data. Watch the short video to see the steps, or follow the written steps. Get the free workbook, to follow along. There's also an interactive pivot table below, that you can try, before you build your own!

.  *  Video: Create a Pivot Table

.  *  Preparing Your Pivot Table Data

.  *  Creating a Pivot Table

.  *  Modifying the Pivot Table

.  *  Try This Pivot Table

.  *  Download the Sample File

.  *  Pivot Table Tools

.  *  More Pivot Table Tutorials

NOTE: This tutorial gives you a quick overview of creating a pivot table. For detailed tutorial, go to the How to Plan and Set Up a Pivot Table page.

.

(Advanced) How to Set Up an Excel Pivot Table

https://www.contextures.com/excelpivottablesetup.html

It’s easy to create a pivot table, with a few mouse clicks. However, the hard part, especially at first, is deciding what goes where. If you aren’t sure where to begin, these steps will help you set up an Excel pivot table.

.  *  Before You Build a Pivot Table

.     1. Check the Source Data

.     2. Set a Goal

.     3. Think About the Layout

.  *  Create a Quick Pivot Table

.  *  Open the PivotTable Field List

.  *  Add Field to Pivot Table Layout

.  *  Remove Field From Layout

.  *  Add More Fields

.  *  Move the Fields

.  *  Add a Filter Field

.  *  Keep Experimenting

.  *  Download the Sample File

.

If you want to see examples of what can be done with "more advanced" pivot tables (don't be scared off by them) Here is a link to a wiki with links to longer demonstration videos.

Actually, what you want to do could be considered a form of "Dashboard", since it will require several pivottables to show everything you want.  If you don't need to see/print all of the individual daily "pick lists", you could do it with a single pivot table that gives you the option of filtering / selecting for one or more days/recipies to generate the summarize PivotTable for.

Power Tool Courses – Wiki – Learn

.

*** Wiki with more specific information and examples *** .

The following Wiki has a collection of links for the various “advanced” Excel “Power” tools. There links to free webinars, short articles and a few free and paid ebooks.

.

I like to download the webinar replay recordings for future reference, and download the example workbooks.  If you are gung ho, you could add time references into the workbook back to the recorded session to make future cross reference easier to find specific features / instructions (I’ve been doing that).

.

These free sessions run roughly 1 hour each followed by a short shill for their related courses (which definitely sound worth the price).

.

Wiki: Get Started with Get & Transform > Tables > PivotTables > PivotCharts > Dashboards ... – PivotTable Wiki

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_o365b/get-started-with-get-transform-tables-pivottables/fea2f31d-7835-4f77-ad34-6fe3e6d07620

Answer 4

I added the links to the OneDrive documents at the top of my response. Did they not work?

Answer 5

Oops, I missed it. Yes it works.

.

I've been playing with it for a while now, not getting anywhere. I'll have to sleep on it. I have some ideas.