sccowl
New Member
I have a financial processing spreadsheet that combines data in two final tables on two tabs for month and YTD info at the end after going through a few processing steps in earlier tabs. It worked beautifully when first built, but over last few months performance has dropped of a cliff, with long delay (5-10 minute) when closing the sheet. Saving before this is fine, taking a couple of seconds. I've rebuilt whole sheet and all seems fine apart from when I add last formulas when performance immediately drops when I try to exit the sheet. There are ~2500 rows per sheet and formula is copied across 24 columns on each, so not that much data. I drag copy the formula to new column each month to minimise processing of unused months (and to ensure formula updates to new monthly column heading) and have also pasted values of earlier months, so there aren't that many calcs.
The issue seems to be the formula below, which combines data from two different tables on earlier tabs. It isn't very complicated so wondering if I've inadvertently included something wrong that is causing lots of processing.
=tbl_Actual_Month[@[Sep-20]]+IF(tbl_TB_Month[@[Department]:[Department]]<>"-",tbl_Adjustments[@[Sep-20]])
The two tables it references are set up in exactly the same structure as the table with the formulas, with 2500 rows and corresponding months. The formula add the corresponding values from two different tables, but only the second one after checking a helper column and only including if not set to a string value of "-".
I wondered if checking this statement against a column in current table was an issue. It also exists in the adjustments table, so have tried
=tbl_Actual_Month[@[Sep-20]]+IF(tbl_Adjustments[@[Department]:[Department]]<>"-",tbl_Adjustments[@[Sep-20]])
but not really change in performance.
I need to use [@[Department]:[Department]] so I can drag copy the formula to update the months but maintain the refence to the department as an absolute reference.
The only other thing to note is that there is some conditional formatting in the final table, to change cell background for older months and to clear for future months. I cleared most of this out when rebuilding, and don't use future months element at all now to try and reduce number of calculations, but doesn't seem to be causing any issue. Conditional formatting are simple references to flags on each column which change background colour.
Can anyone spot anything wrong with the formulas or suggest anything that would improve performance. Doesn't feel like it is anything complicated, and formuals are pretty basic, so can't understand what I am doing, but hopefully someone can point out what is going on. This data feeds into a much bigger reporting spreadsheet, with equivalent structures, but much more complicated processing & alot more data, but although 3 times as big, this calculates really quickly and saves fine.
Help!
The issue seems to be the formula below, which combines data from two different tables on earlier tabs. It isn't very complicated so wondering if I've inadvertently included something wrong that is causing lots of processing.
=tbl_Actual_Month[@[Sep-20]]+IF(tbl_TB_Month[@[Department]:[Department]]<>"-",tbl_Adjustments[@[Sep-20]])
The two tables it references are set up in exactly the same structure as the table with the formulas, with 2500 rows and corresponding months. The formula add the corresponding values from two different tables, but only the second one after checking a helper column and only including if not set to a string value of "-".
I wondered if checking this statement against a column in current table was an issue. It also exists in the adjustments table, so have tried
=tbl_Actual_Month[@[Sep-20]]+IF(tbl_Adjustments[@[Department]:[Department]]<>"-",tbl_Adjustments[@[Sep-20]])
but not really change in performance.
I need to use [@[Department]:[Department]] so I can drag copy the formula to update the months but maintain the refence to the department as an absolute reference.
The only other thing to note is that there is some conditional formatting in the final table, to change cell background for older months and to clear for future months. I cleared most of this out when rebuilding, and don't use future months element at all now to try and reduce number of calculations, but doesn't seem to be causing any issue. Conditional formatting are simple references to flags on each column which change background colour.
Can anyone spot anything wrong with the formulas or suggest anything that would improve performance. Doesn't feel like it is anything complicated, and formuals are pretty basic, so can't understand what I am doing, but hopefully someone can point out what is going on. This data feeds into a much bigger reporting spreadsheet, with equivalent structures, but much more complicated processing & alot more data, but although 3 times as big, this calculates really quickly and saves fine.
Help!