References to external workbooks
 

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

References to external workbooks
 

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

On Jul 27, 4:06*pm, Preston wrote:
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

References to external workbooks
 

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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Pete_UK" wrote:

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

On Jul 27, 4:06 pm, Preston wrote:
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

References to external workbooks
 

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

Preston

"Pete_UK" wrote:

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

On Jul 27, 4:06 pm, Preston wrote:
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

References to external workbooks
 

Thank you. This works for me as well.

"ryguy7272" wrote:

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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Pete_UK" wrote:

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

On Jul 27, 4:06 pm, Preston wrote:
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