| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.programming
|
|||
|
|||
|
Shortening a formula
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)))) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shortening a formula
All I can say is "Geez!" That formula would have me so cross-eyed I'd need
to scratch my eyes with eyeball forks! Can you put the filename in a cell (maybe even on a hidden sheet), name the cell "DailyDBMA" or something, and use that name in your formula? "GTVT06" wrote: 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)))) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shortening a formula
=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)+'[pn]Daily DBMA information'!$D$18:$AG$18)))) Eliminates one of the <=I56 checks if that helps. -- Regards, Tom Ogilvy "GTVT06" wrote: 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)))) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shortening a formula
Right, I know that. But I also wrote "but I'm trying to get the final
result by only using one cell" In other words, I want the calculation to be figured out by only having to take up one cell, I don't want to have to use multiple cells to figure the problems out. But either way it looks like that's what I'm going to have to do. Thanks anyway. Bernie Deitrick wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shortening a formula
That would work, but can you call file paths in a formula from another
cell like that? I don't know if that's possible. Charlie wrote: All I can say is "Geez!" That formula would have me so cross-eyed I'd need to scratch my eyes with eyeball forks! Can you put the filename in a cell (maybe even on a hidden sheet), name the cell "DailyDBMA" or something, and use that name in your formula? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shortening a formula
Tom, you appear to have one extra right parenthesis.
Regards, Greg "Tom Ogilvy" wrote: =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)+'[pn]Daily DBMA information'!$D$18:$AG$18)))) Eliminates one of the <=I56 checks if that helps. -- Regards, Tom Ogilvy "GTVT06" wrote: 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)))) |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shortening a formula
Yes if the other workbook is open - use the Indirect function to build the
reference No if the other workbook is closed - at least know with any builtin functionality. It might be easier to use hardcode defined names (insert=Name=Define) and change that/those. -- Regards, Tom Ogilvy "GTVT06" wrote: That would work, but can you call file paths in a formula from another cell like that? I don't know if that's possible. Charlie wrote: All I can say is "Geez!" That formula would have me so cross-eyed I'd need to scratch my eyes with eyeball forks! Can you put the filename in a cell (maybe even on a hidden sheet), name the cell "DailyDBMA" or something, and use that name in your formula? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shortening a formula
I was just trying to figure that part out. Maybe like this:
ActiveWorkbook.Names.Add _ Name:="DailyDBMA1", _ RefersTo:="'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" ActiveWorkbook.Names.Add _ Name:="DailyDBMA2", _ RefersTo:="'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" in formula use: =IF(I56=TODAY(),"N/A",SUMPRODUCT(--(DailyDBMA1<=I56),DailyDBMA2/(SUMPRODUCT(--( ....etc. "Tom Ogilvy" wrote: Yes if the other workbook is open - use the Indirect function to build the reference No if the other workbook is closed - at least know with any builtin functionality. It might be easier to use hardcode defined names (insert=Name=Define) and change that/those. -- Regards, Tom Ogilvy "GTVT06" wrote: That would work, but can you call file paths in a formula from another cell like that? I don't know if that's possible. Charlie wrote: All I can say is "Geez!" That formula would have me so cross-eyed I'd need to scratch my eyes with eyeball forks! Can you put the filename in a cell (maybe even on a hidden sheet), name the cell "DailyDBMA" or something, and use that name in your formula? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shortening a formula
....or even put the workbook name in a local named cell (to be able to edit it
later) ActiveWorkbook.Names.Add _ Name:="DailyDBMA1", _ RefersTo:=DailyDBMAWorkBook & "!$D$10:$AG$10" ActiveWorkbook.Names.Add _ Name:="DailyDBMA2", _ RefersTo:=DailyDBMAWorkBook & "!$D$18:$AG$18" ActiveWorkbook.Names.Add _ Name:="DailyDBMA3", _ RefersTo:=DailyDBMAWorkBook & "!$D$19:$AG$19" (put the filepath string in the named cell using two tick marks to start, i.e. ''...) "Charlie" wrote: I was just trying to figure that part out. Maybe like this: ActiveWorkbook.Names.Add _ Name:="DailyDBMA1", _ RefersTo:="'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" ActiveWorkbook.Names.Add _ Name:="DailyDBMA2", _ RefersTo:="'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" in formula use: =IF(I56=TODAY(),"N/A",SUMPRODUCT(--(DailyDBMA1<=I56),DailyDBMA2/(SUMPRODUCT(--( ...etc. "Tom Ogilvy" wrote: Yes if the other workbook is open - use the Indirect function to build the reference No if the other workbook is closed - at least know with any builtin functionality. It might be easier to use hardcode defined names (insert=Name=Define) and change that/those. -- Regards, Tom Ogilvy "GTVT06" wrote: That would work, but can you call file paths in a formula from another cell like that? I don't know if that's possible. Charlie wrote: All I can say is "Geez!" That formula would have me so cross-eyed I'd need to scratch my eyes with eyeball forks! Can you put the filename in a cell (maybe even on a hidden sheet), name the cell "DailyDBMA" or something, and use that name in your formula? |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Shortening a formula
Its possible.
I tested on an abstract and then edited the original, so I could have missed taking one off the right end. -- Regards, Tom Ogilvy "Greg Wilson" wrote in message ... Tom, you appear to have one extra right parenthesis. Regards, Greg "Tom Ogilvy" wrote: =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)+'[pn]Daily DBMA information'!$D$18:$AG$18)))) Eliminates one of the <=I56 checks if that helps. -- Regards, Tom Ogilvy "GTVT06" wrote: 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 list | Excel Discussion (Misc queries) | |||
| Shortening or changing a formula | Excel Discussion (Misc queries) | |||
| Shortening a formula | Excel Worksheet Functions | |||
| Shortening a formula | Excel Discussion (Misc queries) | |||
| shortening a forumula | Excel Discussion (Misc queries) | |||
Linear Mode
