Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232

    Unanswered: Help with the INDIRECT function.

    Using the INDIRECT function I'm trying to reference data in another Workbook. However, if that workbook is not open the function returns a #REF! value. Open the Workbook it's referencing and it happily returns the vlaue I need. I understand it's one of the volatile functions in Excel, but I don't know why it won't return return the value if the other sheet is not open.

    Any ideas to help at all please??

    TIA.

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by daveo61
    Using the INDIRECT function I'm trying to reference data in another Workbook. However, if that workbook is not open the function returns a #REF! value. Open the Workbook it's referencing and it happily returns the vlaue I need. I understand it's one of the volatile functions in Excel, but I don't know why it won't return return the value if the other sheet is not open.

    Any ideas to help at all please??

    TIA.
    Thats in the specification for this funtion in the online help: If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

    You could put the reference data in the same workbook on another worksheet. Or create another workbook with VB script, use this as a controlling workbook to open both workbooks.

    Another low tech idea, place a big textbox in the workbook with a note to remind you to open the other workbook.
    ~

    Bill

  3. #3
    Join Date
    Feb 2006
    Posts
    113
    For data from a closed file, John Walkenbach offers a tip via VBA using a function from an old version of Excel. http://www.j-walk.com/ss/excel/tips/tip82.htm HTH

Posting Permissions

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