Indirect Links (Excel 2000 or 2003)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Thanks, Hans.

Reply | Quote

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.

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote