link cells to master excel file, even when new columns are created.

[Nick]

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

[Max]

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 <at>yahoo<dot>com for email
--------------------------------------------------------
"Nick" <nic...@hotmail.com> wrote in message
news:59382ca6.03122...@posting.google.com...

[Nick]

Max,
Brilliant elegant solution.
Thank you very much.
Nick

[Max]

You're welcome, Nick!
Thanks for the feedback.

cheers

Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
--------------------------------------------------------
"Nick" <nic...@hotmail.com> wrote in message
news:59382ca6.03122...@posting.google.com...