Linking data across worksheets

Copper Contributor

I'm sure this question has been addressed many times in different forms. So if someone has a link to direct me to, please help. I have not found anything to explain what I would like to accomplish directly. 

 

What I'd like to do is link data from a master list to multi sheets of the same workbook. 

Here's the set-up. The master list is an inventory list comprised of part #, type, description, and price. These parts are used in different sheets to catalog inventory for different things. What I want to accomplish is this: I want to link the master so that if I update anything (mostly frequently the price) it will automatically update across the entire workbook. 

 

I understand I can match cells. However if I update the master list with another part and that adds a new row, this changes the cell destination. 

 

Ideally I want to create it in a way that revolves around the part number. If I add a part number or change the price of an existing part number, it will update on any other sheet that utilizes that same part #. 

 

Is it even possible to link multiple columns for the same row. 

  example : 

         part #      type              description         price

        12345,   misc. electric,    on/off switch,    $12.00

 

If I create a new table on a different sheet and input part # 12345 into one cell it will fill in the  columns in the row with the linked data, that way anytime I update the master it syncs everywhere that part # is used.

2 Replies

@J_How Sounds to me you can achieve your goal by using a structured Excel table for your master list and refer to that table in your VLOOKUP or INDEX/MATCH functions, whichever you are using.

 

Structured tables expand automatically when you type a new part number below the last row, so that you don't have to update your lookup ranges elsewhere. More about how to use then can be found here

https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-F5ED2452-2337... 

@Riny_van_Eekelen   I'll give this a look over. Thank you for the quick response!