I need speed calculate when I filter rows.

[nader]

hi
Iam
using:=SUMPRODUCT(SUBTOTAL(3,OFFSET(N11:N10000,ROW(N11:N10000)-ROW(N11),0,1)),--(N11:N10000="160m"))formula
in 50 cells and subtotal function in 10 cells and I am working in a bout
10000R*30C cells.When I use auto filter I must wait a few time to calculate
and ready.I need speed calculate.
sorry for language problems.
thanks

 

[Pete_UK]

If you click on Tools | Options | Calculation Tab and set calculation
to Manual rather than Automatic, this will greatly speed up Excel's
filtering. You can force a recalculation while in this mode by
pressing F9. Remember to set it back to Automatic when you are
finished.

Hope this helps.

Pete

 

[nader]

thanks ,but I want both of them.

If you click on Tools | Options | Calculation Tab and set calculation
to Manual rather than Automatic, this will greatly speed up Excel's
filtering. You can force a recalculation while in this mode by
pressing F9. Remember to set it back to Automatic when you are
finished.

Hope this helps.

Pete

 

[Pete_UK]

Not sure what you mean. You can EITHER have calculation set to
Automatic OR to Manual - when in Manual you can force a recalculation
when you want to by pressing F9.

Pete

 

[nader]

sorry, I want filtering and calculating synchronous.is this possible fast.

 

[Pete_UK]

OFFSET is a volatile function, so if there is a change in the
worksheet (like choosing a filter) then Excel will recalculate these
formulae (and dependents) and this is obviously slowing you down.
Perhaps you could look at other ways of achieving the results by
amending the formulae that you have.

Hope this helps.

Pete