If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
link cells to master excel file, even when new columns are created.
I am looking for a way to link data between sheets/files, so that when
I create a new column in Sheet1, this change is reflected in Sheet2. Sheet1: Header1 Header2 Header3 Sheet2: (Headers links to Sheet1) =Sheet1!A1 =Sheet1!B1 =Sheet1!C1 ie: Header1 Header2 Header3 So far, the headers in Sheet2 looks identical to the headers in Sheet1. Problem: If I insert a column in Sheet1 and type in a header, the change is not reflected in Sheet2 : Sheet2: =Sheet1!A1 =Sheet1!B1 no value =Sheet1!C1 ie: Header1 Header2 blank Header3 If I type Header2B in Sheet1, then blank should be reflect this. Any help appreciated. Nick |
#2
|
|||
|
|||
link cells to master excel file, even when new columns are created.
Try this:
Assuming the columns to be linked are from cols A - Z In Sheet2 ------------ Put in A1: =INDIRECT("Sheet1!"&CHAR(COLUMN()+96)&ROW()) Copy across to Z1, then down as many rows as there is data in Sheet1 Now when you insert a column in between cols A-Z in Sheet1, and type-in the new headers, etc in the inserted column, the changes will be reflected in Sheet2 For a cleaner look, suggest that you suppress zeros from showing in Sheet2 Select Sheet2 Click Tools Options View tab Uncheck "Zero values" OK ---------------------------------------------------------------------------- ---- Here's the steps if the cols to be linked extend beyond col Z (some changes needed in the formula for the starting cell at each break of 26 cols) To link cols AA - AZ -------------------------- Put in AA1: =INDIRECT("Sheet1!A"&CHAR(COLUMN()+70)&ROW()) Copy across to AZ1, then down as many rows as there is data in Sheet1 To link cols BA - BZ -------------------------- Put in BA1: =INDIRECT("Sheet1!B"&CHAR(COLUMN()+44)&ROW()) Copy across to AZ1, then down as many rows as there is data in Sheet1 To link cols CA - CZ ------------------------- Put in CA1: =INDIRECT("Sheet1!C"&CHAR(COLUMN()+18)&ROW()) Copy across to CZ1, then down as many rows as there is data in Sheet1 To link cols DA - DZ -------------------------- Put in DA1: =INDIRECT("Sheet1!D"&CHAR(COLUMN()-8)&ROW()) Copy across to DZ1, then down as many rows as there is data in Sheet1 To link cols EA - EZ ------------------------- Put in EA1: =INDIRECT("Sheet1!E"&CHAR(COLUMN()-34)&ROW()) Copy across to EZ1, then down as many rows as there is data in Sheet1 And so on .. The key changes to the formula for the starting cell at each break of 26 cols are (using the formula in EA1 as an example): a. The letter in the phrase e.g.: "Sheet1!E" which has to correspond to the first letter in the starting cell, i.e. "E" in "EA1" in this case b. The number for the "..COLUMN()-34..." part inside the CHAR(...) must equal 97 in the starting cell. So for EA1, as col EA = col # 131 (i.e. : =COLUMN() in say, EA1 returns 131), hence 34 needs to be subtracted from COLUMN() to equal 97 -- hth Max ----------------------------------------- Please reply in thread Use xdemechanik atyahoodotcom for email -------------------------------------------------------- "Nick" wrote in message om... I am looking for a way to link data between sheets/files, so that when I create a new column in Sheet1, this change is reflected in Sheet2. Sheet1: Header1 Header2 Header3 Sheet2: (Headers links to Sheet1) =Sheet1!A1 =Sheet1!B1 =Sheet1!C1 ie: Header1 Header2 Header3 So far, the headers in Sheet2 looks identical to the headers in Sheet1. Problem: If I insert a column in Sheet1 and type in a header, the change is not reflected in Sheet2 : Sheet2: =Sheet1!A1 =Sheet1!B1 no value =Sheet1!C1 ie: Header1 Header2 blank Header3 If I type Header2B in Sheet1, then blank should be reflect this. Any help appreciated. Nick |
#3
|
|||
|
|||
link cells to master excel file, even when new columns are created.
Max,
Brilliant elegant solution. Thank you very much. Nick |
#4
|
|||
|
|||
link cells to master excel file, even when new columns are created.
You're welcome, Nick!
Thanks for the feedback. cheers Max ----------------------------------------- Please reply in thread Use xdemechanik atyahoodotcom for email -------------------------------------------------------- "Nick" wrote in message om... Max, Brilliant elegant solution. Thank you very much. Nick |
Thread Tools | |
Display Modes | |
|
|