Hi,

I have this sheet with almost all formulas of sumproduct. Calculations
based on start/end dates.
It takes quite some time calculating all the formulas.
Do expand the columns to see the sumproduct formulas.

Here I would like to have some macro to run after I mention dates and
calculate all the results.

Sample file is at http://www.savefile.com/files/1621169

Thx.

without looking at your file just use, where b1 has a desired date
=sumproduct((a2:a22=b1 etc
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sinner" wrote in message
...
Hi,

I have this sheet with almost all formulas of sumproduct. Calculations
based on start/end dates.
It takes quite some time calculating all the formulas.
Do expand the columns to see the sumproduct formulas.

Here I would like to have some macro to run after I mention dates and
calculate all the results.

Sample file is at http://www.savefile.com/files/1621169

Thx.

I looked at your file. This is quite some setup you have here. It appears
you have your data in another file and there is a macro named Picture2_Click
associated with your Calculate icon. Have you tried adding a call to the
second macro at the end of the Picture2_Click macro?

Private Sub Picture2_Click()
current code
Macro2
End Sub

Sub Macro2()
some more code
End Sub

If Macro2 tries to run before the formulas are done, then add some delay
before Macro2 runs.

Private Sub Picture2_Click()
current code
'wait 15 seconds
Application.OnTime Now + TimeValue("00:00:15"), "Macro2"
End Sub

Mike F
"Sinner" wrote in message
...
Hi,

I have this sheet with almost all formulas of sumproduct. Calculations
based on start/end dates.
It takes quite some time calculating all the formulas.
Do expand the columns to see the sumproduct formulas.

Here I would like to have some macro to run after I mention dates and
calculate all the results.

Sample file is at http://www.savefile.com/files/1621169

Thx.

I went back and looked at your file. I misunderstood the question. However,
it seems that your formulasations could be modied since you only want the
totals
=SUMPRODUCT(--(ActList=$E6),--(DateList=$C$6),--(DateList<=$C$7),--(CEList=Coll),--(PM=$F$5),(AmtList))
=SUMPRODUCT(--(ActList=$E6),--(DateList=$C$6),--(DateList<=$C$7),--(CEList=Coll),--(PM=$g$5),(AmtList))
=SUMPRODUCT(--(ActList=$E6),--(DateList=$C$6),--(DateList<=$C$7),--(CEList=Coll),--(PM=$hF$5),(AmtList))
to this. Note that you MUST spell it out and can NOT use the reference as
above. Note the { }
=SUMPRODUCT(--(ActList=$E6),--(DateList=$C$6),--(DateList<=$C$7),--(CEList=Coll),--(PM={"cash","cheque","credit
card"}),(AmtList))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...

without looking at your file just use, where b1 has a desired date
=sumproduct((a2:a22=b1 etc
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sinner" wrote in message
...
Hi,

I have this sheet with almost all formulas of sumproduct. Calculations
based on start/end dates.
It takes quite some time calculating all the formulas.
Do expand the columns to see the sumproduct formulas.

Here I would like to have some macro to run after I mention dates and
calculate all the results.

Sample file is at http://www.savefile.com/files/1621169

Thx.

On Jun 21, 8:16*pm, "Don Guillett" wrote:
I went back and looked at your file. I misunderstood the question. However,
it seems that your formulasations could be modied since you only want the
totals
=SUMPRODUCT(--(ActList=$E6),--(DateList=$C$6),--(DateList<=$C$7),--(CEList*=Coll),--(PM=$F$5),(AmtList))
=SUMPRODUCT(--(ActList=$E6),--(DateList=$C$6),--(DateList<=$C$7),--(CEList*=Coll),--(PM=$g$5),(AmtList))
=SUMPRODUCT(--(ActList=$E6),--(DateList=$C$6),--(DateList<=$C$7),--(CEList*=Coll),--(PM=$hF$5),(AmtList))
to this. Note that you MUST spell it out and can NOT use the reference as
above. Note the { * *}
=SUMPRODUCT(--(ActList=$E6),--(DateList=$C$6),--(DateList<=$C$7),--(CEList*=Coll),--(PM={"cash","cheque","credit
card"}),(AmtList))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Don Guillett" wrote in message

...





without looking at your file just use, where b1 has a desired date
=sumproduct((a2:a22=b1 *etc
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sinner" wrote in message
...
Hi,

I have this sheet with almost all formulas of sumproduct. Calculations
based on start/end dates.
It takes quite some time calculating all the formulas.
Do expand the columns to see the sumproduct formulas.

Here I would like to have some macro to run after I mention dates and
calculate all the results.

Sample file is athttp://www.savefile.com/files/1621169

Thx.- Hide quoted text -

- Show quoted text -

Hi Mike,


Thanks for reading the post.
Yes data is in another sheet.
I have named ranges such as ActList for activity list, datelist for
date column, CEList for coll/enter status differentiation, PM for mode
of payment & Amtlist for amount list.
In my sumproduct formula I'm using all these ranges since they change
frequently.

The Picture you noted was a button. By default I have set sheet
calculation to manual.
After I have input data in the data sheet, I put dates & click this
button to calculate. Since formulas are bulky, it takes sometime to
calculate & leaving it to automatic tends to slow up the process. Once
I'm done with data input, then I move on to calculate & finally save.

All I wanted was to have a macro code to get me same figures from the
data table, knowing the formulas that I'm currently using & same name
ranges.

Thx.

--------------------------------
Hieee Don Guillett,

Good to hear from u : )
Thnx for the additional point regarding referencing.

It's a breakup sheet which is pretty simple I guess.
Any alternate approach to get same format breakup results is
welcome : )


Takecare

try this idea

Application.enableevents=false
calculate
Application.EnableEvents=True
rest of code

It's too bad that you didn't bother to see the value of my suggestion to cut
down your calculation time.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sinner" wrote in message
...
On Jun 21, 8:16 pm, "Don Guillett" wrote:
I went back and looked at your file. I misunderstood the question.
However,
it seems that your formulasations could be modied since you only want the
totals
=SUMPRODUCT(--(ActList=$E6),--(DateList=$C$6),--(DateList<=$C$7),--(CEList*=Coll),--(PM=$F$5),(AmtList))
=SUMPRODUCT(--(ActList=$E6),--(DateList=$C$6),--(DateList<=$C$7),--(CEList*=Coll),--(PM=$g$5),(AmtList))
=SUMPRODUCT(--(ActList=$E6),--(DateList=$C$6),--(DateList<=$C$7),--(CEList*=Coll),--(PM=$hF$5),(AmtList))
to this. Note that you MUST spell it out and can NOT use the reference as
above. Note the { }
=SUMPRODUCT(--(ActList=$E6),--(DateList=$C$6),--(DateList<=$C$7),--(CEList*=Coll),--(PM={"cash","cheque","credit
card"}),(AmtList))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Don Guillett" wrote in
message

...





without looking at your file just use, where b1 has a desired date
=sumproduct((a2:a22=b1 etc
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sinner" wrote in message
...
Hi,

I have this sheet with almost all formulas of sumproduct. Calculations
based on start/end dates.
It takes quite some time calculating all the formulas.
Do expand the columns to see the sumproduct formulas.

Here I would like to have some macro to run after I mention dates and
calculate all the results.

Sample file is athttp://www.savefile.com/files/1621169

Thx.- Hide quoted text -

- Show quoted text -

Hi Mike,


Thanks for reading the post.
Yes data is in another sheet.
I have named ranges such as ActList for activity list, datelist for
date column, CEList for coll/enter status differentiation, PM for mode
of payment & Amtlist for amount list.
In my sumproduct formula I'm using all these ranges since they change
frequently.

The Picture you noted was a button. By default I have set sheet
calculation to manual.
After I have input data in the data sheet, I put dates & click this
button to calculate. Since formulas are bulky, it takes sometime to
calculate & leaving it to automatic tends to slow up the process. Once
I'm done with data input, then I move on to calculate & finally save.

All I wanted was to have a macro code to get me same figures from the
data table, knowing the formulas that I'm currently using & same name
ranges.

Thx.

--------------------------------
Hieee Don Guillett,

Good to hear from u : )
Thnx for the additional point regarding referencing.

It's a breakup sheet which is pretty simple I guess.
Any alternate approach to get same format breakup results is
welcome : )


Takecare

On Jun 21, 9:42*pm, "Don Guillett" wrote:
try this idea

Application.enableevents=false
* calculate
Application.EnableEvents=True
rest of code

It's too bad that you didn't bother to see the value of my suggestion to cut
down your calculation time.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Sinner" wrote in message

...
On Jun 21, 8:16 pm, "Don Guillett" wrote:

Don I tried that : ) I placed all three modes together as you said.
={"cash","cheque","credit card"}

but for no cheque & credit card, it is giving a #value error.
so by placing only ={"cash"}, it is working.
The other two may or may not be there but still I would like em to be
calculated. There could also be debit card, pay order & demand
draft : )

How about a data validation, if i select from a cell the mode that I
want or tick all to select the kind of information the grid should
show.
How abt I select the modes : ) wicked ehh :P

Thx.

Let me see YOUR entire formula.
I can't understand why you could get that result from the formula if entered
properly unless a matter of leading/trailing spaces. Send the other file if
you like so I can test. Or, you may try trimming your source as shown below

=SUMPRODUCT(--(ActList=$E6),--(DateList=$C$6),--(DateList<=$C$7),--(CEList=Coll),--(trim(PM)={"cash","cheque","credit
card"}),(AmtList))

Your other idea will not work in this context because, as I said, it must be
spelled out in the formula. Of course, a macro could create the formula.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sinner" wrote in message
...
On Jun 21, 9:42 pm, "Don Guillett" wrote:
try this idea

Application.enableevents=false
calculate
Application.EnableEvents=True
rest of code

It's too bad that you didn't bother to see the value of my suggestion to
cut
down your calculation time.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Sinner" wrote in message

...
On Jun 21, 8:16 pm, "Don Guillett" wrote:

Don I tried that : ) I placed all three modes together as you said.
={"cash","cheque","credit card"}

but for no cheque & credit card, it is giving a #value error.
so by placing only ={"cash"}, it is working.
The other two may or may not be there but still I would like em to be
calculated. There could also be debit card, pay order & demand
draft : )

How about a data validation, if i select from a cell the mode that I
want or tick all to select the kind of information the grid should
show.
How abt I select the modes : ) wicked ehh :P

Thx.