Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shortening a formula
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)))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
adding row to forumla | Excel Discussion (Misc queries) | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |