Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Ref! error using INDIRECT for file name
I'm having trouble with the following formula
=INDIRECT("'S:\14 Finance\SALES\07-08\[Week "&$B41&" 07-08.xls]Summary'!d118") where $b41 refers to a cell with a week number in it. When ever I open this file it returns a #Ref! error unless I open this particular file references in the formula. I need to do this everytime the file is opened, iot doesn't save the values like a link. I tried putting this formula in a SUMPRODUCT() but it made no differnce. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Ref! error using INDIRECT for file name
Some functions don't work unless the workbook containing the info needed is
opened. I"m not sure if this is one of the. Open it and check. -- HTH, Barb Reinhardt "Bentam7" wrote: I'm having trouble with the following formula =INDIRECT("'S:\14 Finance\SALES\07-08\[Week "&$B41&" 07-08.xls]Summary'!d118") where $b41 refers to a cell with a week number in it. When ever I open this file it returns a #Ref! error unless I open this particular file references in the formula. I need to do this everytime the file is opened, iot doesn't save the values like a link. I tried putting this formula in a SUMPRODUCT() but it made no differnce. Any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Ref! error using INDIRECT for file name
From Excel help on the INDIRECT function:
"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." -- David Biddulph "Bentam7" wrote in message ... I'm having trouble with the following formula =INDIRECT("'S:\14 Finance\SALES\07-08\[Week "&$B41&" 07-08.xls]Summary'!d118") where $b41 refers to a cell with a week number in it. When ever I open this file it returns a #Ref! error unless I open this particular file references in the formula. I need to do this everytime the file is opened, iot doesn't save the values like a link. I tried putting this formula in a SUMPRODUCT() but it made no differnce. Any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
#Ref! error using INDIRECT for file name
The good news is that Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/ That includes =indirect.ext() that allows you to return values from closed workbooks. Bentam7 wrote: I'm having trouble with the following formula =INDIRECT("'S:\14 Finance\SALES\07-08\[Week "&$B41&" 07-08.xls]Summary'!d118") where $b41 refers to a cell with a week number in it. When ever I open this file it returns a #Ref! error unless I open this particular file references in the formula. I need to do this everytime the file is opened, iot doesn't save the values like a link. I tried putting this formula in a SUMPRODUCT() but it made no differnce. Any ideas? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error when using Indirect | Excel Worksheet Functions | |||
Error using Excel add-in with INDIRECT.EXT function | Excel Discussion (Misc queries) | |||
Error Embedding Row() within Indirect() | Excel Worksheet Functions | |||
Indirect and Path & File Names | Excel Discussion (Misc queries) | |||
INDIRECT function error | Excel Discussion (Misc queries) |