References to external workbooks

[Preston]

Hi,
I am having trouble using the external references as range arguments in
excel 2007. I was wondering if maybe someone could help. I am using a vlookup
to look at a phone numbers database (worksheet) and lookup the corresponding
name given a phone number. It works fine with local ranges but i tried to
extend the functionality of it by having the phonedatabase workbook separate
from the other workbooks. This way, i can refer to the database range in
vlookup and have different workbooks for different employees that can all
reference the same database of numbers. i can get it to work if the workbook
is open but not when the phone database workbook is closed. if it is closed
it will only work if i use a direct reference to the external range. if i try
to use indirect it does not work. Any suggestions? I am trying to use an
indirect from vlookup to reference a cell in the phone database workbook that
contains the current region (full path) of the phone database table (making
it more extensible as i add and remove numbers) i also tried the indirect
statement locally to a string reference to the external range.
Thank you for any help you can give.

Preston

 

[Pete_UK]

If you check out XL Help on INDIRECT you will see this:

If ref_text refers to another workbook (an external reference), the
other workbook must be open. If the source workbook is not open,
INDIRECT returns the #REF! error value.

So, you can't use INDIRECT to access a closed workbook.

One way round it might be to download the free Morefunc add-in, which
has an INDIRECT.EXT function, which will allow you to do this. Do a
Google search to find sites where you can download Morefunc from.

Hope this helps.

Pete

 

[ryguy7272]

Not sure if Indirect will allow you to access a closed workbook, but Vlookup
certainly will. Take a look at this:
http://www.mrexcel.com/forum/showthread.php?t=71020

HTH,
Ryan---

 

[Preston]

Thank you. This is definitely what the problem was. I didn't read the
indirect help completely. Thank you for your help.

Preston

 

[Preston]

Thank you. This works for me as well.

Not sure if Indirect will allow you to access a closed workbook, but Vlookup
certainly will. Take a look at this:
http://www.mrexcel.com/forum/showthread.php?t=71020

HTH,
Ryan---