Results 1 to 7 of 7
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Indirect in 2007 vs 2003 (2007 vs 2003)

    I'm sure I'm not the first person to record this, nevertheless I found this interesting.
    I'm designing a spreadsheet and using both 2007 and 2003. The Indirect function references an external link. In 2007, the results display nicely without any fuss. In 2003, I need to open the workbook that is being referenced before the results will display.

    Just interesting.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Indirect in 2007 vs 2003 (2007 vs 2003)

    Hmm, that's certainly interesting, since Microsoft claims that external references in INDIRECT still don't work - see INDIRECT function.

    The INDIRECT.EXT function in Laurent Longre's free add-in Morefunc for Excel works with external references in all versions (95 and later) of Excel.

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Indirect in 2007 vs 2003 (2007 vs 2003)

    I'll retest again this evening to be sure.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect in 2007 vs 2003 (2007 vs 2003)

    (XL2007) If I enter the INDIRECT function while the source file is open, I get the values and they remain when the source book is closed. As soon as I do a recalc they get zapped to #REF!'s.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Indirect in 2007 vs 2003 (2007 vs 2003)

    You and Hans are correct - I was wrong (sigh) I must have opened the supporting files. Darn

    However, this formula does work in 2007 =IFERROR(INDIRECT(C5),""), but fails in 2003.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Indirect in 2007 vs 2003 (2007 vs 2003)

    IFERROR is a new function in Excel 2007. In Excel 2003, you must use a combination of IF and ISERROR:

    =IF(ISERROR(INDIRECT(C5)),"",INDIRECT(C5))

    IFERROR is one of the really nice - if long overdue - additions to Excel 2007, together with SUMIFS, COUNTIFS etc.

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Indirect in 2007 vs 2003 (2007 vs 2003)

    I agree, it is an elegant function.
    I just thought I'd somehow forgotten about it - that it is new makes me feel better.
    Attached Images Attached Images
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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