You answered your own question:

I know I can achieve it by putting different
formulas in a couple of seperate cell's

HTH,
Bernie
MS Excel MVP


"GTVT06" wrote in message
oups.com...
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))))