Creating multiple and variable "Parent-Child" relationships in Excel


What is the simplest/best way to set up an Excel spreadsheet so it can manage multiple and varied "Parent-Child" relationships?

For example, a friend of mine, who has a small catering business, recently mentioned that although she loves cooking and interacting with her clients, certain aspects of her food supply management and purchasing are time-consuming and confusing — particularly knowing what amounts of individual items to purchase when she makes her grocery store runs 2-3 times per week.

Now I know that there are multiple software applications out there which are designed to manage exactly this type of situation (and I would be happy to get some suggestions re the best apps). However, I have come across this same (parent-child relationships) question before in other projects so I am very interested in learning how best to set it up in Excel.

My first inclination is to create a list of every possible food item she uses in her cooking and meal preparation. The problem is that while some of the items on the list correspond directly to items to be purchased (eg # of baked potatoes needed = # of Russet potatoes to purchase), other food items are made from a recipe of different items on the list. And some of the latter group may also consist of combinations of yet other food items.

As an example, suppose for an event of 200 people, my friend is planning a buffet which includes the items and quantities shown in Fig. 1. (By the way, she cooks everything from scratch and, once she knows the total number of people to attend, has an uncanny ability to predict just how many servings of each item will be needed.)

2015-11-18a_menu-example-with-number.PNGNow, suppose the last item, Dessert 2, is pumpkin pie with whipped cream. If that is a single item on the food list, then breaking it down to its base ingredients looks something like this (Fig. 2):

2015-11-18b_food-items-with-ingredie.PNGIn this screenshot, each item in Columns 1-4 is an item on the foods list but only those items in a black font belong on the shopping list because the items in a blue font are obviously prepared from other items.

The other issue is, of course, the variable units and the conversions needed between them.

So the objective is to have one sheet tab displaying a simple list of menu options for which my friend can enter the desired number of servings, and a 2nd sheet tab which displays a printable shopping list with the amount of each base item she must either have on hand or purchase.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
have you considered Access?  It is in the Office suite with Excel and is perfect for this type of application ... and several people on this site know it too ~

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I agree. Access would be much better for this purpose. It has some database templates that you might be able to use and customize, but don't forget to buy a good book to accompany it. John Viescas is a great author of Access manuals. The gain of learning Access will be worth the pain of learning the application and it will far outweigh the pain of trying to do this in Excel which I highly recommend you avoid. I learned Access after giving up trying to create a p.o. tracking system in Excel (20 years ago). I did all my customized actions using macros rather than learning to code in VBA and the application made an enormous difference for my client. If money is an issue then you might consider SQL Server Express, which is free, but then you won't have the nice GUI that Access provides.
WeThotUWasAToadAuthor Commented:
Thanks for the responses.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help

here are more resources for learning Access, all free:

Access Basics
Free 100-page book that covers essentials in Access

Learn VBA

free Contact Template for Access :
-- I mentioned this because it has some basic features you can use for your friend's catering business.  It also has a lot of tables you don't need (close to 100 are defined to make it easier for others to hook in, but only about 20 are used by the forms that are set up)

Learn Access Playlist on YouTube

Allen Browne's site:
(Allen's site is a GREAT place to just start reading ...)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.