Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Queensland, Australia
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problems with data source linking

    Hi
    I have two seperate workbooks, one that has a range of data in several worksheets (source workbook), this is then linked to another workbook (destination workbook).

    The linking of the data had been working fine until lately, I believe I know what the problem is I'm just not sure how to fix it.

    Recently I added a number of rows to one of the spreadsheets in the data source workbook and now the references/formulas in the destination workbook are out by the number of rows I have inserted.

    Could someone advise if there is a way that when I add/remove rows/columns in the source worksheet that the destination workbook will automatically adjust to the new rows/columns.

    Thanks in advance for any help
    Cheers
    Dax
    Dax


  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    You can make the source cells a Named Range and refer to that Name in your formula in the destination workbook, rather than referring to specified cells.
    In general, the Named Range will adjust itself to insertions and deletions, whereas a formula in another workbook will not, as you have observed.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Make sure the destination workbook is open at the time you insert the cells.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Queensland, Australia
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by MartinM View Post
    You can make the source cells a Named Range and refer to that Name in your formula in the destination workbook, rather than referring to specified cells.
    In general, the Named Range will adjust itself to insertions and deletions, whereas a formula in another workbook will not, as you have observed.

    Thanks Martin that's worked exactly how I was hoping.
    Cheers
    Dax
    Dax


  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Queensland, Australia
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the advice to keep the destination workbook open while changing. In the workbooks I was asking about it was very quick to change to name references, however I have a couple of other workbooks with much larger data so will use your suggestion with then as I don't have time at present to change all the formulas to name ranges

    Cheers
    Dax
    Dax


Posting Permissions

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