Results 1 to 15 of 15
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Changing link in Destination Workbook (2007)

    I have a workbook named Profit Link. the values in this workbook link to values in a workbook named Cafe Brazilia Sheet1.

    Whenever I insert new rows in Cafe Brazilia , the values in Profit link are not linked to the correct row number in Cafe Brazilia.

    How can i prevent this from happening-i.e if I insert new rows in Cafe Brazilia, then the cells in Profit Link must refer to the correct cell address in Cafe Brazilia?

    Your assistance in this regard will be most appreciated



    Howard

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing link in Destination Workbook (2007)

    Please provide detailed information. Just "the values in Profit link are not linked to the correct row number" doesn't say much.

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Westlake, Ohio, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing link in Destination Workbook (2007)

    It sounds to me like your formulas are using specific locations. i.e. A$6$ This means that if you insert a row before row 6 everything will move down one and your calculation will be pointing at the wrong cell. Given the address I provided it would still be pointing at A6, but the value would now be the one that used to be in A5. Now, without testing I cannot give correct information on how to handle this, but I thought that this might get us started in the right direction. Please let me know if I am even close and then I will start trying to find an answer to the issue.
    ...

  4. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Changing link in Destination Workbook (2007)

    Hi Timelord

    Thanks for the reply , you are on the right track

    Regards

    Howard

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing link in Destination Workbook (2007)

    Instead of linking on a cell, link on a Named Range.

    Click on the cell you want to link, go to InsertNameDefine... and give it a name.

    In your linked sheet, change the formula to reference the Name of the cell instead of the cell reference.

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Changing link in Destination Workbook (2007)

    Hi Hans

    Thanks for the reply. I have attached a sample of my destination workbook, which links to Cafe Brazilia. If I insert new rows in Cafe Brazilia, then the linked row doe not fwere to the correct row in Cafe Brazilia.

    It would be appreciated if you would assist

    Regards

    Howard
    Attached Files Attached Files

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Changing link in Destination Workbook (2007)

    Thanks for the reply. I thought that I would have to do this, but thought that there may be an easier way

    Regards

    Howard

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing link in Destination Workbook (2007)

    If possible, follow mbarron's suggestion of named cells. The disadvantage is that you'll have to name the inserted cells too if you want to refer to them.

  9. #9
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing link in Destination Workbook (2007)

    If you have both books open while you are inserting the rows, the references in the linked book will "self adjust".

  10. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Changing link in Destination Workbook (2007)

    Thanks for the reply.

    Your suggestion is great. I will open the workbook "Profit Link" each time I insert rows in the workbooks that "Profit Link" links to..

    Regards

    Howard

  11. #11
    Lounger
    Join Date
    Jun 2002
    Location
    Westlake, Ohio, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing link in Destination Workbook (2007)

    mbaron is correct, if both are open while making the change it is taken care of right away. The naming will also work, but will take a bit of effort to setup.
    ...

  12. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Changing link in Destination Workbook (2007)

    Hi Hans

    Thanks for the reply. Mbarron's latest suggestion is even better than his first.

    Regards

    Howard

  13. #13
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Changing link in Destination Workbook (2007)

    Hi Timelord

    Thanks for the reply.

    Will use Mbarron's latest suggestion by keeping "profit Link" open when inserting rows in the source workbooks.

    Regards

    Howard

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing link in Destination Workbook (2007)

    It would be even easier if you placed the sheets in the same workbook, but of course that is not always possible.

  15. #15
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Changing link in Destination Workbook (2007)

    Hi Hans

    It would be if there was only one workbook to link to. The example I posted contained only a link to one workbook. I have 13 workbooks to link to. What I will do in future is to have the workbook "profit Link" open when I insert rows in any of the workbooks that "Profit Link" refers to

    Once again thanks for all the input. This site have been a valuable source of information for myself. I have gained tremendous knowledge from all the contributors. The responses have been rather quick.

    Regards

    Howard

Posting Permissions

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