| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)))) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Shortening a formula
Don't'cha just love those ridiculous paths?
You can save a lot of characters by renaming some of the subdirectories (but it's probably too late for that!). Were you around in the good old days when all we had was the 8.3 name convention? We were able to handle things back then just fine! $&*%^ Windows!!!! For example: Departments can be Depts Operations can be Ops DBMA Team can be DBTeam DBMA Reports can be Reports DAILY Daily DBMA information can be DailyDBInfo The file name and the sheet name could also be shortened. All this will add up! Good luck! Biff "GTVT06" wrote in message ups.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)))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Shortening a formula | Excel Discussion (Misc queries) | |||
| Match then lookup | 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) | |||
Hybrid Mode
