Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    56
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Indirect data is not always available

    I'm using WIN 7 and Office 2010.

    I find that when I use Indirect() in an Excel worksheet and refer to a cell in another worksheet, I will not be able to obtain the data unless the other worksheet is open. Once I've opened the other worksheet, I retain the data even if I close the other worksheet.

    I vaguely remember this problem when I used XP and Office 2000. I must have used some workaround then, but I can't remember what it was, since the problem hasn't bothered me again until now, after I've been using Office 2010 for a few months.

    Does anyone know how to solve this problem, so I won't have to open the two worksheets together?

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    A while ago I read about a 3rd party function "Indirect.ext" that was supposed overcome the linking problem. I've never used it.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    INDIRECT only works with open files. Check out this for some workarounds including a link to the Morefunc.dll (which has the indirect.ext function ): http://www.dicks-blog.com/archives/2...sed-workbooks/

    Steve

  4. #4
    Star Lounger
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    56
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks for the help.

    As I read through the links, I see that the problem has been around a long time, and Microsoft has never bothered to do anything about it as they've "upgraded" through various versions of Excel.

    I'm going to try some of the suggestions in the link, to see if they work for me.

  5. #5
    Star Lounger
    Join Date
    Jun 2010
    Location
    Israel
    Posts
    56
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Just to keep you up to date on what I did about this problem.

    I wrote a short macro that automatically opens and closes the other workbook when I open the first workbook.

    It's a ridiculous workaround, but then it's a ridiculous problem. I have the feeling that's probably what I did when I encountered this on Office 2000 a decade or so ago.

Posting Permissions

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