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\Finance\Project_Financial_Reports\1_2005_Reports\QB_Budget.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!Project_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

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" <[email protected]> wrote in message
news:[email protected]...
> 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\Finance\Project_Financial_Report
s\1_2005_Reports\QB_Budget.xls'!Project_Direct_Expenses,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!Project_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

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" <[email protected]> wrote in message
news:[email protected]...
> 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\Finance\Project_Financial_Report
s\1_2005_Reports\QB_Budget.xls'!Project_Direct_Expenses,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!Project_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