Linking two spread sheets and update

%3CLINGO-SUB%20id%3D%22lingo-sub-1323494%22%20slang%3D%22en-US%22%3ELinking%20two%20spread%20sheets%20and%20update%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1323494%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20struggling%20with%20what%20would%20appear%20to%20be%20a%20simple%20taks.%20I%20have%20one%20Master%20table%20with%20numerous%20rows%20and%20columns.%20On%20the%20second%20Sheet%20I%20want%20to%20copy%20all%20the%20values%20in%20the%20all%20the%20rows%20for%20the%20first%20three%20columns%20only.%20Simple%20formula%20%3DSheet1!A1...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20I'm%20having%20is%2C%20when%20I%20add%20new%20rows%20to%20the%20master%20table%2C%20the%20table%20on%20sheet%202%20doesn't%20update%20and%20the%20cell%20A350%20is%20not%20referring%20to%20cell%20%2BSheet1!350%20but%20rather%20Sheet1!351.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%20how%20to%20get%20this%20fixed%3F%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1323494%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1323545%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20two%20spread%20sheets%20and%20update%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1323545%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20very%20fast%20response%20Riny!%20Wasn't%20expecting%20this%20to%20have%20such%20a%20quick%20resolution.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1323537%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20two%20spread%20sheets%20and%20update%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1323537%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F630223%22%20target%3D%22_blank%22%3E%40Camilo1986%3C%2FA%3E%26nbsp%3BIf%20you%20happen%20to%20be%20on%20the%20latest%20version%20Excel%2C%20the%20attached%20workbook%20contains%20an%20example%20that%20will%20work%20for%20you.%20Using%20a%20structured%20table%20of%20in%20Sheet1%20and%20one%20formulae%20in%20A1%20on%20Sheet2%20that%20references%20all%20of%20the%20first%20three%20columns%20of%20the%20table%20in%20Sheet1%20at%20once.%20Add%20records%20at%20the%20bottom%20in%20Sheet1%20and%20the%20list%20in%20Sheet2%20expands%20with%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi all,

 

I have been struggling with what would appear to be a simple taks. I have one Master table with numerous rows and columns. On the second Sheet I want to copy all the values in the all the rows for the first three columns only. Simple formula =Sheet1!A1...

 

The issue I'm having is, when I add new rows to the master table, the table on sheet 2 doesn't update and the cell A350 is not referring to cell +Sheet1!350 but rather Sheet1!351.

 

Any idea how to get this fixed?


Thanks!

4 Replies
Highlighted

@Camilo1986 If you happen to be on the latest version Excel, the attached workbook contains an example that will work for you. Using a structured table of in Sheet1 and one formulae in A1 on Sheet2 that references all of the first three columns of the table in Sheet1 at once. Add records at the bottom in Sheet1 and the list in Sheet2 expands with it.

Highlighted
Thanks for the very fast response Riny! Wasn't expecting this to have such a quick resolution.
Highlighted

@Camilo1986 

Beside Riny's response below, I just figured out another way with relative referencing.

 

=Index('Sheet1!1'A:A,row())

 

Works as well! Hope someone finds this answer useful as well!

Highlighted

@Camilo1986 Indeed, that will work as well. Even a simple =Sheet1!A1:C1 and copy it down as far as needed. But neither of these resize automatically with changes in Sheet1.