Have you considered using the old DOS command SUBST to replace the path with
a single drive letter e.g.

SUBST x: C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\DAILY Daily
DBMA information

then use x:\[Daily account DBMA information 200607.xls] in your formula

Couldn't you name a cell "ThisFile" (Insert/Name/Define) or suchlike, and
put the full path in the named cell, then put ThisFile in your formula? I'm
sure there'd be a way... I'm not familiar with how to, but there must be...

Beege

You can create a defined name in your workbook referring to the other
     oba=[yourfilename.xls]

then use indirect for which the source file must be OPEN
=INDIRECT(oba&"yourworksheet!d140")

GTVT06 wrote...

...

...

Some typos: you've doubled single quotes before the drive letter in all
the external refernces. I'll remove the extraneous ones, reformat and
rewrite your formula with the following replacements: <p> for path, <f>
for filename and <s> for worksheet name.

=IF(I56>=TODAY(),"N/A",
SUMPRODUCT(--('<p>[<f>]<s>'!$D$10:$AG$10<=I56),'<p>[<f>]<s>'!$D$18:$AG$18
/(SUMPRODUCT(--('<p>[<f>]<s>'!$D$10:$AG$10<=I56),'<p>[<f>]<s>'!$D$19:$AG$19)
+SUMPRODUCT(--('<p>[<f>]<s>'!$D$10:$AG$10<=I56),'<p>[<f>]<s>'!$D$18:$AG$18))))

The way you have it written, you're dividing each cell in
'<p>[<f>]<s>'!$D$18:$AG$18 by the sum of two SUMPRODUCT calls. Unless
you're working with values on the order of 1E300, you can & should
defer the division. That is,

Sum[condition, a / (Sum[condition, b] + Sum[condition, c])]
= Sum[condition, a] / (Sum[condition, b] + Sum[condition, c])

Further, since your condition array is the same for all SUMPRODUCTS,
you could reduce your formula to

Sum[condition, a] / Sum[condition, (b + c)]

And since your condition array is 1D and b and c arrays are adjacent,
you could reduce this further to (quasi-Excel syntax)

Sum[condition, a] / Sum[condition * b:c]

That is, try

=IF(I56>=TODAY(),"N/A",
SUMPRODUCT(--('C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56),
'C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$18)
/SUMPRODUCT(('C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$10:$AG$10<=I56)
*'C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$19))

Even so, you're dealing with 3 static ranges,

'<p>[<f>]<s>'!$D$10:$AG$10
'<p>[<f>]<s>'!$D$18:$AG$18
and either  '<p>[<f>]<s>'!$D$18:$AG$19  or  '<p>[<f>]<s>'!$D$19:$AG$19

You could create three defined names, e.g.,

Condition  referring to
='C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$10:$AG$10

CurVal  referring to
='C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$18

BothVals  referring to
='C:\Departments\Operations\DBMA Team\DBMA\DBMA Reports\
DAILY Daily DBMA information\[Daily account DBMA information
200607.xls]
Daily DBMA information'!$D$18:$AG$19

and reduce your formula to

=IF(I56>=TODAY(),"N/A",SUMPRODUCT(--(Condition<=I56),CurVal)
/SUMPRODUCT((Condition<=I56)*BothVals))

then you'd just need to change the defined names each month.