Formula does not update when sorting

[tonyp01]

It seems whenever we sort, the following formulas do not update with the correct row. In the example below the "Updates!$A4" reference stays A4 no matter what row it is sorted to. We have to have our spreadsheet set to manual calculation due to the intensive calculation process (which we only need once a day). I would also not be opposed to recommendations for rewriting this formula all together. I have tried absolute, relative and mixed, but it still stays the same.

=IF(SUMIFS(PaceData!$E:$E,PaceData!$A:$A,Updates!$A4,PaceData!$C:$C,Updates!JD$3)=0,SUMIFS(PaceData!$D:$D,PaceData!$A:$A,Updates!$A4,PaceData!$C:$C,Updates!JD$3),TEXT(SUMIFS(PaceData!$E:$E,PaceData!$A:$A,Updates!$A4,PaceData!$C:$C,Updates!JD$3),"MM/DD/YY")&" A")

[daffodil11]

How many rows are you working with?

Your formula is currently showing undeclared row references, which means you're calculating against 1.04 million rows per column reference.

Even if you're crunching only 150,000 rows, your formula will process in 1/10 the time if you just manually adjusted your references to:

=IF(SUMIFS(PaceData!$E1:$E150000,PaceData!$A1:$A150000,Updates!$A4, PaceData!$C1:$C150000,Updates!JD$3)=0,
    SUMIFS(PaceData!$D1:$D150000,PaceData!$A1:$A150000,Updates!$A4,PaceData!$C1:$C150000,Updates!JD$3),
TEXT(SUMIFS(PaceData!$E1:$E150000,PaceData!$A1:$A150000,Updates!$A4,PaceData!$C1:$C150000,Updates!JD$3),"MM/DD/YY")&" A")

Also, applying a couple of manual line breaks via Alt+Enter makes managing large formulas much, much easier.


As for adjustable references, you may wish to use OFFSET or INDIRECT. However, these are volatile functions and update every time anything changes so they again would take up extra processing power.

Perhaps you could attach an example of your data, and we could proof your table design for inefficiencies.

[tonyp01]

You're exactly right. I set these formulas up prior to knowing how many records the import file would contain. I will definitely change that to "PaceData!$E1:$E150000". The spreadsheet is attached. Any idea why the reference is not updating when sorting?

Thanks

[Speshul]

You are sorting the formulas as well as the data, so their references are moving as well. This will always happen when sorting formulas as far as I am aware, unless you convert to a multi-cell array formula

[daffodil11]

I'm curious to see if the processor heavy formulas could be replace with a VB loop that pulls each corresponding value.

Then, you could sort without volitility, and at whim, update values with another VB run that wipes everything out and pulls fresh.

I will tinker as I find free time.

At any rate, here's the original version updated with declared ranges. My computer calculates this in about 45 seconds.