Linking data across worksheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1451296%22%20slang%3D%22en-US%22%3ELinking%20data%20across%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1451296%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20sure%20this%20question%20has%20been%20addressed%20many%20times%20in%20different%20forms.%20So%20if%20someone%20has%20a%20link%20to%20direct%20me%20to%2C%20please%20help.%20I%20have%20not%20found%20anything%20to%20explain%20what%20I%20would%20like%20to%20accomplish%20directly.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I'd%20like%20to%20do%20is%20link%20data%20from%20a%20master%20list%20to%20multi%20sheets%20of%20the%20same%20workbook.%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20set-up.%20The%20master%20list%20is%20an%20inventory%20list%20comprised%20of%20part%20%23%2C%20type%2C%20description%2C%20and%20price.%20These%20parts%20are%20used%20in%20different%20sheets%20to%20catalog%20inventory%20for%20different%20things.%20What%20I%20want%20to%20accomplish%20is%20this%3A%20I%20want%20to%20link%20the%20master%20so%20that%20if%20I%20update%20anything%20(mostly%20frequently%20the%20price)%20it%20will%20automatically%20update%20across%20the%20entire%20workbook.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20understand%20I%20can%20match%20cells.%20However%20if%20I%20update%20the%20master%20list%20with%20another%20part%20and%20that%20adds%20a%20new%20row%2C%20this%20changes%20the%20cell%20destination.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIdeally%20I%20want%20to%20create%20it%20in%20a%20way%20that%20revolves%20around%20the%20part%20number.%20If%20I%20add%20a%20part%20number%20or%20change%20the%20price%20of%20an%20existing%20part%20number%2C%20it%20will%20update%20on%20any%20other%20sheet%20that%20utilizes%20that%20same%20part%20%23.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20even%20possible%20to%20link%20multiple%20columns%20for%20the%20same%20row.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20example%20%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3CU%3Epart%20%23%3C%2FU%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3CU%3E%20type%26nbsp%3B%3C%2FU%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3CU%3Edescription%3C%2FU%3E%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3CU%3Eprice%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2012345%2C%26nbsp%3B%20%26nbsp%3Bmisc.%20electric%2C%26nbsp%3B%20%26nbsp%3B%20on%2Foff%20switch%2C%26nbsp%3B%20%26nbsp%3B%20%2412.00%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20create%20a%20new%20table%20on%20a%20different%20sheet%20and%20input%20part%20%23%2012345%20into%20one%20cell%20it%20will%20fill%20in%20the%26nbsp%3B%20columns%20in%20the%20row%20with%20the%20linked%20data%2C%20that%20way%20anytime%20I%20update%20the%20master%20it%20syncs%20everywhere%20that%20part%20%23%20is%20used.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1451296%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1451419%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20data%20across%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1451419%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F694129%22%20target%3D%22_blank%22%3E%40J_How%3C%2FA%3E%26nbsp%3BSounds%20to%20me%20you%20can%20achieve%20your%20goal%20by%20using%20a%20structured%20Excel%20table%20for%20your%20master%20list%20and%20refer%20to%20that%20table%20in%20your%20VLOOKUP%20or%20INDEX%2FMATCH%20functions%2C%20whichever%20you%20are%20using.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStructured%20tables%20expand%20automatically%20when%20you%20type%20a%20new%20part%20number%20below%20the%20last%20row%2C%20so%20that%20you%20don't%20have%20to%20update%20your%20lookup%20ranges%20elsewhere.%20More%20about%20how%20to%20use%20then%20can%20be%20found%20here%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FUsing-structured-references-with-Excel-tables-F5ED2452-2337-4F71-BED3-C8AE6D2B276E%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FUsing-structured-references-with-Excel-tables-F5ED2452-2337-4F71-BED3-C8AE6D2B276E%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1451477%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20data%20across%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1451477%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BI'll%20give%20this%20a%20look%20over.%20Thank%20you%20for%20the%20quick%20response!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted

@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... 

Highlighted

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