Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, Australia, Australia
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have two Excel 2007 sheets. I'm trying to use a range from one sheet in a vlookup function in the other. If I have both sheets open, it works fine. If I have the source closed, I get #ref errors. In Edit Links the status for the link is shown as "Error: Undefined or non-rectangular name".

    More details: In the source sheet, I have a range that is populated from an access database (set to manual refresh). This named range is used in the vlookup function in the second sheet (eg sample syntax is =VLOOKUP(A10,'I:\SOURCE.xlsm'!ProjectList,2,0) )

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What is the refersto formula for the defined name?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, Australia, Australia
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In the Name Manager in the source worksheet, the name ProjectList has a Refers to: value of "=Table_ProjectManagement". In turn, the name Table_ProjectManagement has a Refers to: value of "=Projects!$A!4:$G$175" (dimmed).

    Changing the name ProjectList to refer directly to the explicit range has fixed the problem.

    Thanks.

    How did this indirect naming come about? What should I do to stop it happenning in future?

Posting Permissions

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