Linking two spread sheets and update

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!

5 Replies

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

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

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

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

@Camilo1986 

 

I am having a similar issue, I have a master sheet with all names, start dates and dates of birth among another information. On sheet 2 I just want the name, start date and DOB to automatically update. i have used the formula ='Staff Master - Alpha'!A4 to populate this, but when ever i add a new column to sheet 1 it doesnt update on sheet 2 unless i go an manually add that column in. I just want it to automatically add those columns in/remove them when we delete them without me having to do anything to the second sheet.