"Ignore" formulas in hidden rows

[henrik2h]

Hi
I have a fairly large sheet that contain cash flows on a monthly basis for 15 years (in columns).
Our chart of accounts have roughly 100 accounts and there are 10 division so 1000 rows. There are relatively simple if-statements in all cells which distribute cash flows, but apparently quite a few cells (12*15*1000).
Now the question, many of the accounts are regularly not used and also not all divisions so they can be hidden with a regular filter. However, the formulas still need to recalculate I guess, is there any chance of "turning off" the calculation for hidden cells? Or make the formula very efficient and stop when row is hidden?

VBA would be a last resort only.

Any ideas?

 

[johnny51981]

Using a SUBTOTAL function as a part of your IF statements would help you out there, as opposed to SUM or using the traditional arithmetic symbols (+,-,*,/) Here is a link to the Microsoft Page that shows the different Function Numbers you would use as a part of your SUBTOTAL component, note the column that states "ignores hidden rows".

SUBTOTAL function - Microsoft Support

How to use the SUBTOTAL function in Excel to return a subtotal in a list or database.

support.microsoft.com

 

[henrik2h]

Thank you for your reply. However, I am trying to speed up my spreadsheet which is rather slow. I can’t remove the formulas since I don’t know which accounts or divisions that will be used. But since often 50-75% of the formulas are not actually used I am looking for a way to either turn calculation off for these hidden cells or make an efficient formula that will evaluate very fast for the hidden cells. Subtotal has nothing to do with it.

 

[henrik2h]

Unless I completely misunderstand your proposal