Linking Worksheets (Excel 2002)

Topic

New Reply

Simple Example: I have data in sheet 1 a1:a10. I need to link this to Sheet 2. I go to sheet 2 / Cell A1 and enter the following: =Sheet1!A1. I do that all the way through A10 and sheet 2. If I change data in sheet 1 cell A1 it is changed in Sheet 2 cell A1. (GREAT). Here is my problem – If I insert a row above cell A1 in Sheet 1. I enter data in cell A1 of sheet1. I go to Sheet 2 and it does not show the new data that I put in sheet 1 cell A1 in sheet 2. I need a way of linking sheets, so if I insert rows or delete rows in Sheet 1, it is reflected automatically in sheet 2?

Reply | Quote

Replies

A few questions:

Do you have the most efficient design for the task in hand?
Why would you need to insert a row above A1 or delete rows?

If you need to insert or delete rows on Sheet1 why not reinsert the formulae manually or write a macro run from a command button to automatically udate the formuale in Sheet2?

Reply | Quote

How about something like:

=INDIRECT(“‘Sheet1’!”&CELL(“address”,A1))

You can copy it thru the relevent range in Sheet2.

You could also use INDEX to get the relevant info.

=INDEX(Sheet1!$A$1:$A$10,ROW())
or
=INDEX(Sheet1!$A$1:$E$10,ROW(),column())

if you use multiple columns and rows.

Another way (if you only need a display and not the actual data is to define the range in sheet1 as dynamic range name with OFFSET, then paste a picture of this range in sheet 2. The picture will expand as the range is expanded, by insert or even adding new entries (if set up correctly)

One question: Why do you need to have a duplicate of the data in Sheet1? It just seems redundant. What are you trying to ulitmately accomplish by doing this, there might be other tricks to get the data you need.

Steve

Reply | Quote