1) INDIRECT()
This is a volatile function. It updates constantly. Every cell with an INDIRECT() formula will recalculate every time you change anything anywhere.
That makes INDIRECT() a tough choice when you're putting it 1000s of cells. You have two FULL columns of them on the OCT sheet.
==========
TO REMOVE INDIRECT():
On your
LIST page, I created a new named range:
ALLMONTHS
=List!$E$3:$CG$600
This is a table of all your possible months. With this, we will us a standard 2-dimensional INDEX/MATCH/MATCH to do the look with no INDIRECT().
You can erase IT2 and IT3.
In E4:
=IF($C4="", "", INDEX(AllMonths, MATCH($C4, List!$D$3:$D$600, 0), MATCH($IT$1, List!$E$1:$CG$1, 0)))
In F4:
=IF($C4="", "", INDEX(AllMonths, MATCH($C4, List!$D$3:$D$600, 0), MATCH($IT$1, List!$E$1:$CG$1, 0) + 1))
Copy those two formulas down.
Do this on all the sheets where you have those INDIRECT() still operating, as do many of your hidden sheets. Better...flatten the previous sheets by removing ALL formulas from them so you know the values won't change for prior months.
1) Highlight the entire sheet
2) Press
CTRL-C
3) Select
EDIT > PASTE SPECIAL > VALUES
Now the data is permanent and no more calcs from this sheet will slow down sheet performance.
===========
Another way to do M4:
=IF(COUNT(C4:I4)<>7, "", G4 + MIN(I4,E4) + MIN(F4,H4) + K4)
===========
On the
LIST page you have a
whole column COUNTIF() formula in the Conditional formatting of column A....worse...you've also entered the same CF formula for two separate conditions, that means it is fully checking TWICE every time it fails, for every cell!
Also, you applied that CF to the entire column...65k cells instead of just the 500 cells that make your database. If you want to go a few 100 rows past, that's one thing, but to go 65k rows past...don't do that.
Also, your CF formula is doing a COUNTIF() on the entire column. It would be best to limit that as well, perhaps to 600 rows, that should be plenty.
Same thing on the TELEPH_LIST sheet. The CF should only be applied to about 300 rows, and then change the CF formulas to the same 300 rows.
The CF formulas on the
CHECKING sheet are much better..except they are applied to the entire column.
===============
You've colored the background of every cell on the
INDEX sheet. Color just enough to get the display you want.
===============
On the
REPORT page you don't need to run the ENTIRE formula twice
in D5 to test for the ISNA(), you can just test the MATCH().
D5:
=IF(ISNA(MATCH(B5,List!$A$1:$A$500,0)),"",INDEX(List!$E$1:$E$500,MATCH(B5,List!$A$1:$A$500,0)))
===========
Your sheet does not have 90mb worth of data. You have "sheet bloat" that is frequently caused by formatting applied to entire sheets. Your sheet is actually only about 9mb.
I went through all the sheets and removed all the formatting from a few 100 rows past your data to the ends of the sheets. You dropped 82mb in size.
I've attached a copy of your sheet with most of the stuff we went over here so you can see.
https://sites.google.com/a/madrocke...WTELEPHONES-W-Macros--.zip?attredirects=0&d=1