+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : INDIRECT, ADDRESS and an external spreadsheet

  1. #1
    Registered User
    Join Date
    09-07-2009
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    26

    INDIRECT, ADDRESS and an external spreadsheet

    Hi All
    (MODERATORS sorry corrected my formula and now the title post is wrong! no longer using ADDRESS)

    1st up, Thank You for all the help in the past. Here's another problem

    Firstly I dont even know if this is possible - Help was not much help, and I can't find a similar post.

    Is it possible to modify the INDIRECT function to include the path and file name?

    I have this formula
    =LOOKUP(9.99999999999999E+307,INDIRECT("'"&$B203&"'!B2:B"&ROWS(B$1:B203)))
    I need it to return a value from a cell in another spreadsheet. Lets say the directory is
    "d:\test stuff"
    and the file is
    Slope.xlsx

    Cheers

    Sam
    Last edited by samtoucan; 12-12-2009 at 03:20 AM. Reason: Wrong Formula !

  2. #2
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: INDIRECT, ADDRESS and an external spreadsheet

    Hi,

    If you dealing with a closed file, you will need to use the UDF called PULL() designed by Harlan Grove ...

    HTH

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: INDIRECT, ADDRESS and an external spreadsheet

    You can also investigate morefunc.xll c/o Laurent Longre and specifically the INDIRECT.EXT function.

    There was a thread opened in the Development Forum a week or so ago by someone with an add-in related to this matter - I can't say I've looked at it but the OP claims it's quicker than both PULL and the above - whether this true or not I have no idea but it may also be worth further investigation on your part ?

  4. #4
    Registered User
    Join Date
    09-07-2009
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: INDIRECT, ADDRESS and an external spreadsheet

    Not familiar with modules at all so may be making some fundamental errors.
    Also please attached.

    In my Analysis spreadsheet, which 'calls' the data spreadsheet called 'Slope'...
    OK ALT+F11 to open VBA. Right clicked Modules, and Imported Module.
    no probs so far
    Saved as a XLSM file (which permit macros).


    Now to use the module... I changed INDIRECT to INDIRECTEX, and added the path to the Slope file.
    =LOOKUP(9.99999999999999E+307,INDIRECTEX("'D:\!Sam\A_Projects\Forex\[slope.xlsx]!"&$B203&"'!B2:B"&ROWS(B$1:B203)))
    but it returns #value!

    Note the formula works correctly when the data is in the Analysis spreadsheet.

    Am I doing something fundamentally wrong, like path name not enclosed in quotes or something? Doco on INDIRECTEX appears to be scarce.
    Will also try the PULL function on Monday - more posts in the forum for that.

    PS
    I wanted to split the Analysis from the Data sheets because the Analysis component opened much faster without the semi-static Data sheets (they only need to be calculated once). Was hoping the Data sheets could be left closed (take a while to load).

    Cheers

    Sam
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: INDIRECT, ADDRESS and an external spreadsheet

    Hi,

    Laurent's function is named : =INDIRECT.EXT()

    HTH

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1