Hi

I have a excel workbook that users use for LEAN rapporting. On the first sheet they enter date, department in a form...and it works fine except it is very slow when user klicks on Save button which is on the same sheet.

The workflow is like this.

Comboboxes fetches menu items from MenuItems sheet(works 100%)
When user is finished with the form on Input sheet and klicks on Save button -> 2seconds later info is written to the Database sheet.



For some reason it takes almost 2sec to update the Database sheet. I have been watching on two screens the update and it gets slow when it fetches data from Department combobox and then it goes fast until it fetches data from Status combobox.

I can't see why is this happening so maybe some of you guys could help me solve this mystery?

Attached Files

Leanarkiv 2012-test.xlsm (60.1 KB, 13 views)

I narrowed it down to the formula that I use SUMPRODUCT is what is causing the slowdown:

=SUMPRODUCT((Department="Red")*(Status="Start"))
Is there a better way to do this?

Instead of using full columns for your defined ranges maybe you could use Dynamic ranges

Fi the range named "department". instead of defining the range as "D: D""( which is very long) try
And the same for your other named ranges

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post