Can someone help me shorten this formula? I'm not sure if the
mathamatics can be shortened by altering the formula or using a
different formula to figure out the problem, but the path name makes it
exceed the max amount of charachters. Changing the linking files path
location is not an option. I know I can achieve it by putting different
formulas in a couple of seperate cell's but I'm trying to get the final
result by only using one cell, I know the path name can be shortened by
using '[pn] instead, but the name of the spreadsheet changes every
month, so I can't easily find a replace 200607.xls to 200608.xls next
month for the cell's with these formulas like I can on all of the other
formulas, since the formula will not contain 200607.xls if I use '[pn].

*The value of I56 is a date

I would like to be able to shorten this formula to be able to show the
files full path location:

=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\O perations\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\O perations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA
information'!$D$19:$AG$19)+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\O perations\DBMA
Team\DBMA\DBMA Reports\DAILY Daily DBMA information\[Daily account DBMA
information 200607.xls]
Daily DBMA information'!$D$18:$AG$18))))

This is how I currently have it, but it creates problems, when the path
needs to be changed every month:

=IF(I56=TODAY(),"N/A",SUMPRODUCT(--('[pn]Daily DBMA
information'!$D$10:$AG$10<=I56),'[pn]Daily DBMA
information'!$D$18:$AG$18/(SUMPRODUCT(--('[pn]Daily DBMA
information'!$D$10:$AG$10<=I56),'[pn]Daily DBMA
information'!$D$19:$AG$19)+SUMPRODUCT(--('[pn]Daily DBMA
information'!$D$10:$AG$10<=I56),'[pn]Daily DBMA
information'!$D$18:$AG$18))))