linking worksheets

[jpknig2]

Is there a way to dynamicly link worksheets, such that new inserted
lines in the source sheet are also added to the linked sheet?

 

[ShaneDevenshire]

Hi,

We need more detail:

in Sheet2 if you have

=SHEET1!A1
=SHEET1!A2

in cells A1 and A2

What do you want to happen when you insert a row at row 2 in sheet1?

1. Do you want the formulas to follow the old cells?
2. Do you want a new line at the bottom of the sheet2 links to reference the
new line in sheet1
3. Do you want a new line in sheet2 inserted at the same position as the new
line in Sheet1 and do you want that new line to have formulas that refer to
the new line?

 

[pdberger]

Yep. The easy way to do it is to start in the 'target' cell, where you want
the info to end up. Type an = sign to start the formula, then simply navigate
to the cell(s) where the source data is on the other pages. Click on them
and either use them directly or in a formula.

The formula in the target page (say, it's 'Sheet2') will look like this.
Note the single quotes and exclamation point:

='Sheet1'!A1
or
= 2 * 'Sheet11'!A1

HTH

 

[jpknig2]

Hi,

We need more detail:

in Sheet2 if you have

=SHEET1!A1
=SHEET1!A2

in cells A1 and A2

What do you want to happen when you insert a row at row 2 in sheet1?

1. Do you want the formulas to follow the old cells?
2. Do you want a new line at the bottom of the sheet2 links to reference the
new line in sheet1
3. Do you want a new line in sheet2 inserted at the same position as the new
line in Sheet1 and do you want that new line to have formulas that refer to
the new line?

I want to know how to do # 2 and # 3 above. I already have all the
rows in Sheet2 two linked to a corresponding row in Sheet1. Some of
the cells in Sheet2 have formulas using data from Sheet1. What I want
is to be able to insert new rows or add new rows to the end of Sheet1
and have this new data automatically populated in Sheet2. I want this
to work without me having to open Sheet2 and and create new links for
the new rows (either manually or by clicking and dragging an adjoining
row).
Thanks,
Jessie

 

[ShaneDevenshire]

Hi,

Well if you don't mind the new data being at the bottom, then you can add
the new line in the first sheet at the bottom and by changing your formula on
the second sheet it can appear to be automatic.

Instead of =Sheet1!A1
use =IF(Sheet1!A1="","",Sheet1!A1)

Copy this formula down as far as you want - the most number of rows you
expect to get data in for.

in this case if there is no data in a cell on the first sheet nothing will
show on the second sheet and in this case data must be added below previous
data on the first sheet. If you need to "insert rows" between other rows on
sheet1 and have those entries show up on sheet2 then a much more complicated
approach is needed:

=IF(INDIRECT(ADDRESS(ROW(),COLUMN(),,,"Sheet2"))="","",INDIRECT(ADDRESS(ROW(),COLUMN(),,,"Sheet2")))

If you really want it automatic - to create the formulas as new data is
entered you will need to use VBA.