Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Recurring Problems with Links with Full Path Names
I have two workbooks, both of which are in the same directory, one
referencing cells in the other. The dependent workbook (the one containing the reference to the source workbook) displays a #VALUE! error. The formula in the cell is: =IF(Budget_Date="none",0,DSUM('N:\Corporate\Financ e\Project_Financial_Reports\1_2005_Reports\QB_Budg et.xls'!Project_Direct_Expenses,Project_ID,Revenue _Criteria)) (Note the fully qualified path.) If I remove the path name, the #ERROR! goes away and the correct value is shown. The new formula in the cell is: =IF(Budget_Date="none",0,DSUM(QB_Budget.xls!Projec t_Direct_Expenses,Project_ID,Revenue_Criteria)) However, every time I save the worksheets and reopen them, the filespec reverts to the fully qualified filespec above and re-displays the #ERROR! result. Can someone explain what is going on? When does Excel convert to the fully qualified filespec? I tried things such as opening the source file first, then the dependent file, but this doesn't seem to help. Why is the fully qualified filespec showing the #VALUE! error? Am I exceeding some length limit? If so, what is the limit? Thanks, JMcBeth |
#2
|
|||
|
|||
It's because DSUM (or any D* function) does not work if the other workbook
is not open, since you can't have the source closed you must use '[workbook_name.xls] you can use SUMPRODUCT instead if you plane to have the source closed, here's an example http://tinyurl.com/56zae you might also want to look at pivot tables -- Regards, Peo Sjoblom "JMcBeth" wrote in message ... I have two workbooks, both of which are in the same directory, one referencing cells in the other. The dependent workbook (the one containing the reference to the source workbook) displays a #VALUE! error. The formula in the cell is: =IF(Budget_Date="none",0,DSUM('N:\Corporate\Financ e\Project_Financial_Report s\1_2005_Reports\QB_Budget.xls'!Project_Direct_Exp enses,Project_ID,Revenue_C riteria)) (Note the fully qualified path.) If I remove the path name, the #ERROR! goes away and the correct value is shown. The new formula in the cell is: =IF(Budget_Date="none",0,DSUM(QB_Budget.xls!Projec t_Direct_Expenses,Project_ ID,Revenue_Criteria)) However, every time I save the worksheets and reopen them, the filespec reverts to the fully qualified filespec above and re-displays the #ERROR! result. Can someone explain what is going on? When does Excel convert to the fully qualified filespec? I tried things such as opening the source file first, then the dependent file, but this doesn't seem to help. Why is the fully qualified filespec showing the #VALUE! error? Am I exceeding some length limit? If so, what is the limit? Thanks, JMcBeth |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change the Excel Title Bar to show the full file path na... | Excel Discussion (Misc queries) | |||
Problems opening Excel files using DFS links | Excel Discussion (Misc queries) | |||
Change Path names in copied work book | Excel Worksheet Functions | |||
Hyperlinks Problems | Excel Worksheet Functions |