Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,424
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Named range in destination Workbook (2007)

    I have created a range name Dividends_Received in the destination workbook that refers to the range of data as follows in the source workbook

    =SUM([M_ECMHO.xls]sheet1!$R$1499,[M_ECMHO.xls]sheet1!$T$1499:$U$1499)

    when I type =Dividens_Received in the Destination workbook, I get the correct value. However, If I insert rows in the source workbook my value changes.

    How can I prevent this from happening. IF I create a range name in the destination workbook called for eg Dividends_Received and it refers to =SUM([M_ECMHO.xls]sheet1!$R$1499,[M_ECMHO.xls]sheet1!$T$1499:$U$1499), how can It set the range to refer to the correct cell number even if new rows are inserted

    Your assistance will be most appreciated

    Regards

    Howard

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

    Re: Named range in destination Workbook (2007)

    You'll always run into problems like this if you refer to cells in another workbook.

    As long as your destination workbook is open while the source workbook is modified, formulas and defined names in the destination workbook referring to the source workbook will be updated automatically if cells are inserted or deleted.

    But if the destination workbook is closed while the source workbook is modified, it doesn't "know" about the changes, so formulas and defined names aren't updated.

    So you should either combine the worksheets into one workbook, or make sure that the destination workbook is always open while the source workbook is being modified.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,424
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Re: Named range in destination Workbook (2007)

    Hi Hans

    Thanks for the info

    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
  •