Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shortening references to external workbooks | Excel Discussion (Misc queries) | |||
References to external workbooks in excel | Excel Discussion (Misc queries) | |||
inserting sheets into existing workbooks w/o external references | Excel Worksheet Functions | |||
External References | Excel Discussion (Misc queries) | |||
External References not working right. | Excel Worksheet Functions |