Hello everyone,
My Dad has a small catering business, they spend a lot of time hand writing lists, so I'm trying to help him out with a better solution. I am trying to create a list generator based on items selected on a main sheet. I have created 4 worksheets. This is just a small portion, I will be adding more items as I work through it. Ideally the final list generated only contains items with actual value, not all items.
1st worksheet is a listing of each item to be used in a drop down for selecting items on another worksheet.
2nd worksheet is a breakdown of the ingredients and quantity required(Min 50 people, goes up by 25)
3rd is the will be the menu selection-drop down item selection
4th is a generated list
Based on the menu items selected on worksheet 3, I need to generate a shopping list of ingredients based on the item selected and the number of people. I've been trying ifs and vlookups. I'm thinking Index match might be better, but haven't really got a good understanding of that one yet. I'm guessing a VBA solution is probably best, but my skills there are more of a cut and paste, trial and error approach. This is my first post with the mini-sheet, so hopefully that is correct.
My Dad has a small catering business, they spend a lot of time hand writing lists, so I'm trying to help him out with a better solution. I am trying to create a list generator based on items selected on a main sheet. I have created 4 worksheets. This is just a small portion, I will be adding more items as I work through it. Ideally the final list generated only contains items with actual value, not all items.
1st worksheet is a listing of each item to be used in a drop down for selecting items on another worksheet.
| Catering.xlsx | |||
|---|---|---|---|
| A | |||
| 2 | Caesar Salad | ||
| 3 | Macaroni Salad | ||
| 4 | Garden Salad | ||
| 5 | Potato Salad | ||
Menu Item-datasource | |||
2nd worksheet is a breakdown of the ingredients and quantity required(Min 50 people, goes up by 25)
| Catering.xlsx | |||||||
|---|---|---|---|---|---|---|---|
| A | B | C | D | E | |||
| 1 | Main Item | Ingredient | 50 | 75 | 100 | ||
| 2 | Caeser Salad | Romain Lettuce | 3 | 5 | 6 | ||
| 3 | Caeser Salad | Croutons | 2 | 3 | 4 | ||
| 4 | Caeser Salad | Caesar Dressing | 1 | 2 | 2 | ||
| 5 | Macaroni Salad | Elbow Pasta | 2 | 3 | 5 | ||
| 6 | Macaroni Salad | Mayo | 1 | 2 | 2 | ||
| 7 | Macaroni Salad | Onion | 1 | 1 | 2 | ||
| 8 | Macaroni Salad | Pickle | 1 | 1 | 2 | ||
| 9 | Macaroni Salad | Cheese | 1 | 1 | 2 | ||
| 10 | Garden Salad | ||||||
| 11 | Garden Salad | ||||||
| 12 | Garden Salad | ||||||
| 13 | Garden Salad | ||||||
| 14 | Garden Salad | ||||||
| 15 | Potato Salad | ||||||
| 16 | Potato Salad | ||||||
| 17 | Potato Salad | ||||||
| 18 | Potato Salad | ||||||
| 19 | Potato Salad | ||||||
Ingredients | |||||||
3rd is the will be the menu selection-drop down item selection
| Catering.xlsx | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | # of People | 51 | ||||
| 2 | ||||||
| 3 | Salad #1 | Caesar Salad | ||||
| 4 | Salad #2 | Macaroni Salad | ||||
| 5 | Salad #3 | |||||
Menu Selection | ||||||
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| B3:B5 | List | ='Menu Item-datasource'!$A$2:$A$5 |
4th is a generated list
| Catering.xlsx | ||||
|---|---|---|---|---|
| A | B | |||
| 1 | Item | Quantity | ||
| 2 | ||||
Grocery List | ||||
Based on the menu items selected on worksheet 3, I need to generate a shopping list of ingredients based on the item selected and the number of people. I've been trying ifs and vlookups. I'm thinking Index match might be better, but haven't really got a good understanding of that one yet. I'm guessing a VBA solution is probably best, but my skills there are more of a cut and paste, trial and error approach. This is my first post with the mini-sheet, so hopefully that is correct.
