Shortening a formula

  • Thread starter Thread starter GTVT06
  • Start date Start date
G

GTVT06

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\Operations\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\Operations\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\Operations\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))))
 
You answered your own question:
I know I can achieve it by putting different
formulas in a couple of seperate cell's
Click to expand...

HTH,
Bernie
MS Excel MVP


GTVT06 said:
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\Operations\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\Operations\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\Operations\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))))
Click to expand...
 
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?
 
=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 said:
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\Operations\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\Operations\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\Operations\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))))
Click to expand...
 
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.
 
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.
 
Tom, you appear to have one extra right parenthesis.

Regards,
Greg

Tom Ogilvy said:
=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 said:
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\Operations\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\Operations\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\Operations\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))))
Click to expand...
Click to expand...
 
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.
 
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.
 
....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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
Tom, you appear to have one extra right parenthesis.

Regards,
Greg

Tom Ogilvy said:
=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 said:
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\Operations\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\Operations\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\Operations\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))))
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Shorten Formula 10
IF(AND(Complex Formula in Error in data import before I process it 8
Sub to open n close .xls files in folders 5
trying to find solution to copy duplicated data from one sheet to 12
WCG Stats Wednesday 10 February 2021 1
WCG Stats Thursday 21 January 2021 1
WCG Stats Sunday 20 December 2020 1
WCG Stats Tuesday 09 March 2021 1

Back
Top