I think I need some more expert help. Here’s the background on what I’ve got and what I’m trying to do. I’ve created a base with three tables. The Master Menu Table is a repository of menus. The User’s Weekly Menu table is where I am pulling menus from the Master Menu Table to create weekly menus. The Shopping List is where the weekly shopping list is held. More details:
Master Menu Table: This table holds my master menus and is where ingredients and their amounts are listed so they can be rolled up to a weekly shopping list. I chose a multiple select field for the weeks that are assigned so I wouldn’t have to enter menus that are repeated from week to week. The goal of the Master Menu Table was to have recipes entered only one time. The fields with multiple select years and weeks solved one problem (not having to enter the weekly recipes over and over again), but it created several others, the biggest of which is I had to create separate columns for each week’s ingredient amounts so they could be rolled up. I’m sure there are formulas or a better underlying structure that would simplify lots of this.
User’s Weekly Menus: The purpose of this table is to provide a place to draw from the master menus to create the weekly menus. Recipes are pulled in from the Master Menu table. Over time, I will have enough recipes in my Master Menu Table to start recycling the recipes in different combinations in the User’s Weekly Menu table. Already the breakfast recipes are the same from week to week.
Shopping List: This food list is where I am rolling up the weekly totals from the columns in the Master Menu Table. On the far right is the column where I would like to just bring in the current week’s menus but because of the multiple select field where the year and weeks are assigned, it’s pulling in all the weeks. When I tried to write an If/then formula to bring in the current week (example: 6), it brings in those that are ONLY assigned to week 6, not those that contain week 6 (because of the multiple select field). I haven’t figured out a way to list ONLY the current week’s menus that are related to the ingredients on the shopping list. (The initial problem I posted about.) I want to be able to do this so if I choose NOT to make the meatloaf that week, I know which ingredients to disregard on the shopping list.
When I first read your responses, I thought perhaps I needed to move away from the multiple select field for the year and week assigned, but to do so creates a need to recreate/duplicate some of the recipes each week and I don’t want to do that. I would greatly appreciate any help or advice you could provide. Thanks!