Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Indirect Links (Excel 2000 or 2003)

    Hi All,

    I am using a formula with vlookups, concatenate, and indirect to locate a value in a different workbook. The formula works ... but, it appears to only work when the other workbook is opened. When I open the workbook with these formulas, all the formulas go "REF". When I open the source workbook, the formulas work. This is very frustrating! Is there something about "Indirect" that could be causing this ... ?

    B4 is simply a 5-digit text field used to form the desired workbook
    A10 and C9 are used to form the desired worksheet's name
    B10 is used to locate the desired cell.

    =VLOOKUP($B10,INDIRECT(CONCATENATE("'[",$B$4," Mass Balance Spreadsheets.xls]",$A$10," ",C$9,"'!","$A$12:$P$100")),16,FALSE)

    As always, any help/advise is greatly appreciated ...

    --cat

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

    Re: Indirect Links (Excel 2000 or 2003)

    If you use INDIRECT to refer to another workbook, that workbook must be open, otherwise it will return #REF, as you have found.

    The free add-in MoreFunc by Laurent Longre provides a function INDIRECT.EXT that can handle references to closed workbooks. You can download it from Excel add-ins.

  3. #3
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect Links (Excel 2000 or 2003)

    Hans, how do you know so much?!

    I am currently downloading the add-in and will place a post with how it works ...

    Thanks!
    --cat

  4. #4
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect Links (Excel 2000 or 2003)

    The new function, Indirect.ext, works perfect for me!

    Thanks, Hans.

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

    Re: Indirect Links (Excel 2000 or 2003)

    Just remember, if you want to distribute your workbook to others, they will need to install Morefunc too.

    If you have the time and inclination, take a look at the help that comes with the add-in (Tools | Morefunc | Help) to see if there are other functions you can use.

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Indirect Links (Excel 2000 or 2003)

    Hi Cindy,

    An alternative solution, that doesn't require the MOREFUNC addin to be available, is to explicity link to the other workbook via a 'helper' worksheet (which you could hide) and use your INDIRECT function to point to that.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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