Please help – optimizing heavy workbook containing volatile functions

[plshelpexcel]

Dear all,


I have a workbook that recently became heavy and unstable. The simplest of data entries and calculations are now extremely slow and laggy. Furthermore, this is a workbook that will need to be constantly updated with more data.


I’m looking for suggestions on how to optimize what I believe is creating the bulk of the problem, either through VBA (pretty limited experience here) or more sophisticated and non-volatile functions. Any help whatsoever would be greatly appreciated. THANK YOU!!!

---

All data is contained within the same workbook (no external links / references).


Tab CalcSheet is where the majority of my formulas are located (think of it as a summary output & control tab).


Within CalcSheet:


Column I contains several dates, starting in I8. The current date range goes from I8:I3000 (ascending order), for context.

Column K contains the sum of the values in L:AI. For example, K8=SUM(L8:AI8).


The issue seems to come from the formulas in L8:AI3000, and this is where I could use help!


For example:

L8=IF($I8<=$D$6,SUMIFS(INDIRECT("'"&L$6&"'!"&"$IT$"&L$4&":$IT$"&L$5&""),INDIRECT("'"&L$6&"'!"&"$B$"&L$4&":$B$"&L$5&""),$I8),0)
Where:

- I8 is a lookup value (a date, as described above).

- D6 is a cutoff date.

- L6 contains the name of the tab within the same workbook where the data is located. These names vary per column and were hardcoded in L:AI. For example, L6=DataSheet1, M6=DataSheet2, N6=DataSheet3, etc.


- "IT" is the column reference for the DataSheets where the data I want to sum-up is located.


- L4 contains the row number where the target range begins. The formulas in L4:AI4 were entered manually. For example, L4=ROW('DataSheet1'!$IT$33), M4=ROW('DataSheet2'!$IT$33), N4=ROW('DataSheet3'!$IT$33), etc. Note that the target range always starts in row 33 in the DataSheets.


- L5 contains the row number where the target range ends. Again, these were all entered manually as ROW formulas, however, the last row for the target range is unique to each DataSheet. As such, I had to manually find and link the last row for each ROW formula in L5:AI5. Furthermore, these formulas will need to be continuously adjusted as each DataSheet will be continuously updated with new data.

- "B" is the column reference for the DataSheets where dates are located. You will notice that these are tested against values in column I in CalcSheet.

Any thoughts or suggestions?

 

[igold]

A quick thought, you may want to check for phantom ranges, meaning if you go to each sheet and hit CTRL+End to find out where Excel thinks your last cell with data is. If you have any large phantom ranges they can cause the symptoms you are describing...

 

[plshelpexcel]

A quick thought, you may want to check for phantom ranges, meaning if you go to each sheet and hit CTRL+End to find out where Excel thinks your last cell with data is. If you have any large phantom ranges they can cause the symptoms you are describing...

Thank you. Checked and that doesn't seem to be the problem...