Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
External reference with variable workbook name?
Hello everyone,
I'm referencing an external workbook and need part of the workbook name to be a variable. My base formula is; [55421GNTForms.xls]SBR'!$C$5 I need "55421" to be a variable, with the new information pulled from a cell within the referencing workbook. (55421 is a name, not a number.) There will be a .xls file of the correct name already saved in the correct referenced location. What is the syntax for this reference? I've entered: ["A1"&GNTForms.xls] SBR!$C$5 and numerous other combinations and have not had any luck. I'm in the 'New Users' Group for a reason, so please dumb you answer down as much as possible. I would greatly appreciate it. Thanks. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
External reference with variable workbook name?
First, this will only work if the workbook is OPEN. If the workbook is closed, you'll need to have INDIRECT.EXT within MOREFUNC.xll installed. http://xcell05.free.fr/ Let's say the file is in the following directory in your system: A1: C:\KenV Documents\folder1 (location of the file you want) B1: 55421 C1: = "'"&A1&"\["&B1"]55421GNTForms.xls]SBR'!$C$5" D1: =indirect.ext(C1) I hope I dumbed it down enough. It took me a while to figure out myself. Come back if you can't get it working. Barb Reinhardt "KenV" wrote: Hello everyone, I'm referencing an external workbook and need part of the workbook name to be a variable. My base formula is; [55421GNTForms.xls]SBR'!$C$5 I need "55421" to be a variable, with the new information pulled from a cell within the referencing workbook. (55421 is a name, not a number.) There will be a .xls file of the correct name already saved in the correct referenced location. What is the syntax for this reference? I've entered: ["A1"&GNTForms.xls] SBR!$C$5 and numerous other combinations and have not had any luck. I'm in the 'New Users' Group for a reason, so please dumb you answer down as much as possible. I would greatly appreciate it. Thanks. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
External reference with variable workbook name?
Hello Barb,
I can't seem to get it to work. It's giving me the "Formula contains an error" message and it's highlighting B1"]GNTForms.xls]SBR' Am I correct in assuming I need to take the '55421' out of the C1 formula? That's what the ["&B1"] is for, right? That's the variable and I don't want it in there twice. I have the external workbook open and the Morefunc turned off. Thanks for the help. "Barb Reinhardt" wrote: First, this will only work if the workbook is OPEN. If the workbook is closed, you'll need to have INDIRECT.EXT within MOREFUNC.xll installed. http://xcell05.free.fr/ Let's say the file is in the following directory in your system: A1: C:\KenV Documents\folder1 (location of the file you want) B1: 55421 C1: = "'"&A1&"\["&B1"]55421GNTForms.xls]SBR'!$C$5" D1: =indirect.ext(C1) I hope I dumbed it down enough. It took me a while to figure out myself. Come back if you can't get it working. Barb Reinhardt "KenV" wrote: Hello everyone, I'm referencing an external workbook and need part of the workbook name to be a variable. My base formula is; [55421GNTForms.xls]SBR'!$C$5 I need "55421" to be a variable, with the new information pulled from a cell within the referencing workbook. (55421 is a name, not a number.) There will be a .xls file of the correct name already saved in the correct referenced location. What is the syntax for this reference? I've entered: ["A1"&GNTForms.xls] SBR!$C$5 and numerous other combinations and have not had any luck. I'm in the 'New Users' Group for a reason, so please dumb you answer down as much as possible. I would greatly appreciate it. Thanks. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
External reference with variable workbook name?
Oops, I forgot something
C1: = "'"&A1&"\["&B1"]55421GNTForms.xls]SBR'!$C$5" to C1: = "'"&A1&"\["&B1&"]55421GNTForms.xls]SBR'!$C$5" "KenV" wrote: Hello Barb, I can't seem to get it to work. It's giving me the "Formula contains an error" message and it's highlighting B1"]GNTForms.xls]SBR' Am I correct in assuming I need to take the '55421' out of the C1 formula? That's what the ["&B1"] is for, right? That's the variable and I don't want it in there twice. I have the external workbook open and the Morefunc turned off. Thanks for the help. "Barb Reinhardt" wrote: First, this will only work if the workbook is OPEN. If the workbook is closed, you'll need to have INDIRECT.EXT within MOREFUNC.xll installed. http://xcell05.free.fr/ Let's say the file is in the following directory in your system: A1: C:\KenV Documents\folder1 (location of the file you want) B1: 55421 C1: = "'"&A1&"\["&B1"]55421GNTForms.xls]SBR'!$C$5" D1: =indirect.ext(C1) I hope I dumbed it down enough. It took me a while to figure out myself. Come back if you can't get it working. Barb Reinhardt "KenV" wrote: Hello everyone, I'm referencing an external workbook and need part of the workbook name to be a variable. My base formula is; [55421GNTForms.xls]SBR'!$C$5 I need "55421" to be a variable, with the new information pulled from a cell within the referencing workbook. (55421 is a name, not a number.) There will be a .xls file of the correct name already saved in the correct referenced location. What is the syntax for this reference? I've entered: ["A1"&GNTForms.xls] SBR!$C$5 and numerous other combinations and have not had any luck. I'm in the 'New Users' Group for a reason, so please dumb you answer down as much as possible. I would greatly appreciate it. Thanks. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
External reference with variable workbook name?
This is what finally worked. A co-worker of mine figured it out.
=CONCATENATE("'C:\Mitek62\jobs\",B10,"\[",B10,"GNTForms.XLS]","SBR'","!$AS$7") I haven't addressed the INDIRECT issue yet. Thank you for your consideration. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic External Workbook Formula | Excel Worksheet Functions | |||
Finding an external reference on a worksheet | Excel Discussion (Misc queries) | |||
Appending external worksheets into existing workbook? | Links and Linking in Excel | |||
Help to import data from reference workbook | Excel Discussion (Misc queries) | |||
Change workbook sheet reference using cell A1 to change a vairable | Excel Worksheet Functions |