Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Posts
    309
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking Worksheets (Excel 2002)

    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?

  2. #2
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Worksheets (Excel 2002)

    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?
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Linking Worksheets (Excel 2002)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •