Results 1 to 4 of 4

morefunc indirect.ext

This is a discussion on morefunc indirect.ext within the Excel Questions forums, part of the Question Forums category; i'm trying to take advantage of the indirect.ext function in the morefunc.xla add-in. it's a great tool. my question is ...

  1. #1
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,470

    Default morefunc indirect.ext

    i'm trying to take advantage of the indirect.ext function in the morefunc.xla add-in. it's a great tool. my question is this: when the file my function is referencing closes, i only get one good set of numbers. upon a recalculation, all computations return with the #REF! error like the book does not exist. i think my syntax is right. what am i doing wrong?

    my formula looks like this (Q5 in example):

    =INDIRECT.EXT("[WTR04.xls]"&P5&"!$BN$34")

    if i use the full path as suggested by the formula, my formula looks like this (Q4 in the example):

    =INDIRECT.EXT("C:\Documents and Settings\All Users\Documents\[WTR04.xls]Mar04!BK35")

    the latter equation will always return the #REF! error. i've read mr longre's help files, but can't seem to nail down what i'm doing wrong here. an example looks like this:

    ******** ******************** ************************************************************************>
    Microsoft Excel - TKN Loading Send 04-05.xls___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    P
    Q
    R
    S
    3
    dategal.tkn.lbs.
    4
    Mar04#REF!******63.00*#REF!
    5
    Apr04#REF!******52.20*#REF!
    Farm1*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    as it stands i'm utilizing the first of the two equations and trying not to recalculuate when using this workbook, but that is growing cumbersome as it's evolved to a 'daily-entry' type workbook.

    if anyone has any ideas/suggestions, i'd love to hear them. thanks!
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  2. #2
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,080

    Default Re: morefunc indirect.ext

    I have never used this before but after looking at the help file, your syntax looks like it's missing the single quotes:

    This is the example from the help file:
    =INDIRECT.EXT("'C:\My Documents\[Workbook5.xls]Sheet1'!A9")

    Based on your 2nd sample and the help file, maybe:
    =INDIRECT.EXT("'C:\Documents and Settings\All Users\Documents\[WTR04.xls]Mar04'!BK35")

  3. #3
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,470

    Default

    HOTPEPPER!!!

    that was it! oh my, it is the little things isn't it! i really missed those didn't i ! thank you!
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  4. #4
    New Member
    Join Date
    Aug 2009
    Location
    Australia
    Posts
    10

    Default Re: morefunc indirect.ext

    I Know this is an old thread but some problems never die.
    Excel 2013 and still indirect wont work with closed books.

    I am needing to reference a closed book and have previously used the following:
    =IF(E38="","",VLOOKUP(E38,'S:\PB\[2014-04-01-InternalCatalog.xlsx]Catalog'!$A$2:$H$1000,2,FALSE))
    Its clunky but it works.

    Then I thought I would make it a bit better as updates were painful in having to change many formulas on a page

    I modified the formula to:
    =IF(E37="","",VLOOKUP(E37,N6&"!$A$2:$H$1000",2,FALSE))
    Where N6 = 2014-04-01-InternalCatalog.xlsx]Catalog
    this gave me #Value! error

    I then again modified the formula to:
    =IF(E35="","",VLOOKUP(E35,INDIRECT.EXT(N6&"!$A$2:$H$1000"),2,FALSE))
    Using the Indirect function was not an option as it doesn't work on closed books so I installed the indirect.ext add in.
    This gave me the #Ref! error

    Thinking it may be the cell value causing the error, I substituted the cell for the actual value and tried this:
    =IF(E36="","",VLOOKUP(E36,INDIRECT.EXT("'S:\PB\[2014-04-01-InternalCatalog.xlsx]Catalog'!$A$2:$H$1000"),2,FALSE))
    This gave me the #N/A error

    I am stumped as to what the error is or how to resolve it

    All I wish to do is have the filename as a value in a cell so that when the book is updated, I only have to change one cell value not 50

    Any and all suggestions will be most welcome.

    Thanks,
    Inta.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com