Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
variable substitution in a formula linking to external workbook
Hi,
I'm trying to link to an external workbook. Let's say the formula is: G:\2007\2007 Jun\blah.xls I would like to be able to change "Jun" by referencing a cell. I've read that the Indirect function cannot be used to reference an external workbook without it being open. I can't open my external workbook b/c it has the same file name as the one I'm working on (but they are located in difference folders, thus, needing to change "Jun" each month). Is there a way to do this? Thanks in advance for any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
variable substitution in a formula linking to external workbook
Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/ That includes =indirect.ext() that may help you. my wrote: Hi, I'm trying to link to an external workbook. Let's say the formula is: G:\2007\2007 Jun\blah.xls I would like to be able to change "Jun" by referencing a cell. I've read that the Indirect function cannot be used to reference an external workbook without it being open. I can't open my external workbook b/c it has the same file name as the one I'm working on (but they are located in difference folders, thus, needing to change "Jun" each month). Is there a way to do this? Thanks in advance for any help. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
variable substitution in a formula linking to external workbook
I downloaded the formulas, but I am getting an error. I can't see the
mistake in using the INDIRECT.EXT formula. Can anyone with experience tell me by looking why this would be incorrect? =INDIRECT.EXT("'W:\Transportation\Overseas Operatations\FCA\FCA Reports and Updates\FCA In Transit Reports\["&TEXT(B2,"yyyy.mm.dd")&" FCA in transit report SZH.xls]Po List'!$A$3") It works when the reference sheet is open, but returns the #VALUE! error when I close that sheet... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
variable substitution in a formula | Excel Discussion (Misc queries) | |||
External reference with variable workbook name? | New Users to Excel | |||
Dynamic External Workbook Formula | Excel Worksheet Functions | |||
Linking a cell to another workbook cell based on a variable name | Excel Discussion (Misc queries) | |||
Linking formula to external spreadsheet | Excel Discussion (Misc queries) |