How to update all linked workbooks upon change

[waffleicious]

Hello all.

I'm a chef at a restaurant in NC and I've been developing a workbook that contains a reference of all of the ingredients that I purchase with their pack size, unit cost, etc...
I use this workbook as a reference for other workbooks that I build for specific recipes. The recipes reference the base sheet for price and pack size of ingredients so that when an ingredient's price changes, I don't have to redo all the calculations myself.

The problem I run into is that the cells have to stay exactly where they are in order for them to remain properly linked -unless every linked workbook is open at the same time. In order to add new ingredients, I have to add them to rows at the bottom of the sheet, which becomes cumbersome as I cannot insert rows alphabetically without messing up all my referenced workbooks. I have 50+ recipes so far, so opening them all at once isn't really doable.

Is there a way for me to insert rows and move data around while still having all of the files that reference it change too?



Also, this is my first post and I hope it is in the right category and considered a "function" of Excel. Thanks

[Richard Buttrey]

Hi, and welcome to the forum.

Without a macro you might consider using a VLOOKUP making sure the range you lookup would cover any additional rows you might add.

[waffleicious]

Thank you for the suggestion! I think VLOOKUP will meet my needs but I'm having an issue working with it.

I am getting the value "#REF!" after inputting the code.

The goal is to lookup the UPC in Column 1 and then pull the name and other information associated with the column of the UPC.

Here is my code.

Please Login or Register  to view this content.

So A5 is the UPC for the item listed in the Sample Recipe.
'Base Inventory Sample.xlsm'!Table2[Column1] Is the spreadsheet and column to reference to find the UPC code.
",2," is the value in the second column that I want to pull. In this case, it should be the name "Butter"
"FALSE" means that I only want to look for the exact value.

Am I doing this right?

I have attached a sample Base Inventory and a Sample Recipe for reference.

To explain my sheets in a little more detail, I also have a macro running to record the date that the price of each ingredient was updated. It records this value a couple columns over on the same row as the ingredient. Furthermore, the Recipe references the Base Inventory for prices of ingredients and then once it has calculated the total cost of the recipe, that number is linked back to the Base Inventory sheet labeled "Prep" so that I have the cost of the prepped ingredients in the Inventory sheet as well.

Let me know if you have any more questions. I really appreciate any and all help.
These sheets have been my babies that I've worked on for the last year and getting this sorted would be so exciting for me!!



EDIT:
I found that if I change the lookup to the entire table, the VLOOKUP works correctly. Now, my problem is that my table will continue to grow as I add ingredients. How do I make sure that the table referenced will grow as well? Is there an End of List function or something that determines I just want to look at all the data in the table regardless of table size?

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

The Vlookup should be

Please Login or Register  to view this content.

As long as new items are added to the row immediately below the last row in the table, the Table name definition automatically expands to include new rows so you shouldn't have to worry about it.

Personally I dislike tables since I find the structure of functions that use them less than intuitive. I always tend to work with Dynamic Named ranges which achieve the same sort of thing and which I find easier to read and understand.

[waffleicious]

Thanks Richard I see that it does, indeed work that way!

If I were to make this a Dynamic Named Range, how would I go about that?
I found this formula to set the range of all items with a value in column a

Please Login or Register  to view this content.

But then how would I reference a value in column a and have it tell me the corresponding row values in columns B,C, etc...?