Performance - formula referencing multiple tables

[sccowl]

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!

 

[sccowl]

Fixed although I still don't really understand what is wrong with original formula, although think it is something to do with how I have set up the absolute reference.

However, by turning off structured referenced and using regular referencing, it instantly fixed the problem. Saves back in under a second. I've seen structured references can be slow, but in my experience they have generally worked really well. The formula worked, but I must have set up wrongly somewhere along the way, but can't see where, unlike with a more standard formula. Everything still set up in a data table as before.

If anyone interested, you can switch off Structured References in Options. In the Working with Formulas section, uncheck the box that says “Use table names in formulas”. You can still use the references manually, and it doesn't impact anything that is set up, but also allows regular cell address referencing.

Would still appreciate any guidance if anyone can spot what I've done wrong.

Original
=tbl_Actual_Month[@[Sep-20]]+IF(tbl_TB_Month[@[Department]:[Department]]<>"-",tbl_Adjustments[@[Sep-20]])

New, equivalent using regular referencing
=Actual_Month!BS12+IF($D12<>"-",Adjustments!BS12,0)

 

[XOR LX]

Hi,

I seem to recall (though I can't locate the documentation now) that one of the (sadly, still considerable) disadvantages of Structured Referencing in formulas is that the necessary construction for absolute column referencing, for example:

tbl_TB_Month[@[Department]:[Department]]

is, unlike its non-Structured Reference equivalent, volatile.

I have seen workbooks which rely heavily on this quite natural syntax - to allow copying of formulas to the right whilst maintaining an absolute reference to some fixed column within the table - become practically unusable due to this inherent volatility.

I would suggest that you use the Find & Replace tool to replace all these references with their relative-referencing equivalents, i.e. replace all occurrences of:

[@[Department]:[Department]]

with

[@Department]

and see if that improves performance.

Regards