• Linking Worksheets (Excel 2002)

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Linking Worksheets (Excel 2002)

    Author
    Topic
    WSd_perotto
    AskWoody Lounger
    October 31, 2002 at 3:36 pm #378767

    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
    Viewing 1 reply thread
    Author
    Replies
    • WSbandido
      AskWoody Lounger
      October 31, 2002 at 4:01 pm #628054

      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
    • WSsdckapr
      AskWoody Lounger
      October 31, 2002 at 4:51 pm #628075

      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
    Viewing 1 reply thread
    Reply To: Linking Worksheets (Excel 2002)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    June 2025
    S M T W T F S
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.