0

I've got a master inventory price sheet for all the ingredients we use in our recipes that I want to reference in all our recipe files to dynamically calculate costs. This way if we change suppliers on an ingredient all of the costs are adjusted through all recipe sheets automatically. Here's what I've done so far:

  1. Aggregating the multiple tabs on the master pricing sheet into one (called "All") using this:

=query({'Flours, starches, dry goods'!A2:V;Bread!A2:V;Produce!A2:V;'Refrigerated and frozen'!A2:V;'Oils, vinegars, milks, condiments'!A2:V;'Canned Goods'!A2:V;Spices!A2:V;Packaging!A2:V;Consumables!A2:V}," select * where Col1 is not null  ",0)

Master Price Sheet

_____________________________________________________________

  1. Use IMPORTRANGE to import them to a "Reference" tab on the recipe file:

=IMPORTRANGE("1-LACG2FbYIyZ2sykuYlxm2KqPVQ0kdFt4UpS9hFjvDw","All!A2:V100")

Recipe file with tab linked o master price sheet

_____________________________________________________________

  1. Referencing the prices like any other cell on another tab from within the recipe sheet. Ingredient costs linked to cells in price reference tab

The problem with this method is that if you move any of the rows out of alignment on the original file (say, adding a new row for a new ingredient in the master price sheet) it throws off all the reference points in the recipe itself.

I'm thinking I need a new way to tackle this problem. Any suggestions? Any help would be greatly appreciated!

  • pls, share a copy of your sheets for further help. – user0 Mar 31 at 22:31
0
  • paste this into 'Nacho sauce'!B2 (after you delete everything in B2:B):

    =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, 'Price Sheet Reference'!A2:F, 5, 0)))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.