I recently took on the responsibility of ordering and organizing some special edition supplies in my workplace. I have created a spreadsheet but I know there is probably so much more I could do with excel. If someone could steer me in the right direction, I will read and research to learn how to do it. I work in a clinic and am working with series of allergens (liquid chemical in little bottles and/or syringes), but to make this a bit easier to understand I will call them recipes and ingredients.
There are about 15 recipes and each has there own ingredients. The # of ingredients vary from 5 to 75. Some of the recipes have ingredients that are used in other recipes. Each ingredient has a different shelf life.
So far, I have made an Excel workbook with multiple sheets, one for each recipie. On each worksheet, I have listed the ingredients by name, article #, and expiration date. If we have more than one in stock, I enter additional expiration dates cell by cell moving to the right. I was able to do conditional formatting to highlight expired in red and nearly expired in yellow.

Part of what I need to do, is know if there is an ingredient in one of the other recipes available that can be borrowed from, so as to not have to order more if it could be shared between. Also, I need to determine when it makes more sense to order the entire recipie ingredients or to order the individual ingredient. I just don' know enough about Excel to know if these kinds of things should be done by macro, pivot table or using MS Access or if there is any other better options.

Any help would be appreciated