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

    Using Formula in Range Name (2007)

    I have a workbook "A" which has a linked formula to another worksheet as follows:

    =-SUM(Salesdata.xls!NVSales)

    In the workbook Salesdata I have named the range "NVSales" in the refers to section I have set this to =sum(R1680,T1680:U1680)

    The formula & range name works fine except if I close the workbook "SalesData" and then do a recalculation. The formula containing =-SUM(Salesdata.xls!NVSales) then comes up with #ref!

    If I open Salesdata.xls then the correct value comes up containing the formula =-SUM(Salesdata.xls!NVSales)

    It would be appreciated if you could assist

    Regards

    Howard

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

    Re: Using Formula in Range Name (2007)

    Excel can't evaluate defined names in closed workbooks.

    You should define the name in the workbook containing the formula.
    The defined name should refer to the range R1680,T1680:U1680 in the appropriate sheet in SalesData.xls.
    You can then use this name in formulas even if SalesData.xls is closed.

    BTW there is no need to use SUM both in the definition of the defined name AND in the formula.

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

    Re: Using Formula in Range Name (2007)

    Hi Hans

    Thanks for the advise

    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
  •