Unexplained performance hit on some worksheets but not others, related to named ranges.

    Objection: Assumes facts not in evidence.


    Treating the root cause is how problems get fixed. Anything short of that is known as treating a symptom.


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

    As far as I can see the problem is in some of the formulas used in your CF on those sheets, namely the ones that compare a cell to the row above using a syntax like:


    INDEX(namedrange,ROW())=INDEX(namedrange,ROW()-1)


    The problem with that is that you apply it from row 1, which means that for that row your comparison is to INDEX(namedrange,0) which will return every cell in the range (and as mentioned, your named ranges refer to entire columns), so that's a lot of comparisons! I suggest you either only apply that from row 2, or change it so that it uses something like MAX(1,ROW()-1) instead.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Love 1

    rory (or groot, if you prefer), thank you! That is exactly the issue.


    I have confirmed this by going to the 'cs' sheet and removing named ranges one by one until I found the problematic one. Then I added them all back in and removed all conditional formats that did not reference that named range. In the end, the combination of one named range and two conditional formats was sufficient to produce the problem. And when I modified the conditional format formulas, the problem abated.


    I was incorrect to state up front that all of the same conditional formatting on the slow sheets was present on the normal sheet. The sheet that was working well did not have the previous-row comparison.


    The ::facepalm:: thing is that I had all the information I should have needed to discover this much sooner, if I'd just taken a more careful analytical approach. I knew that the four problematic sheets were created to handle a one-to-many relationship on one of the columns, and I was the one that developed the CF to make rows that had a common value in one of the columns appear grouped. What I don't know is why I only began experiencing the slowdown recently, instead of immediately when I added the problematic CF.


    In any event, you have added to my knowledge with your observation that INDEX(namedrange,0) will return every cell in the range. I had never made that connection before!


    Thanks again!


    Now, how do I mark this thread resolved?


    Grits ain't groceries

    Eggs ain't poultry

    And the truth value of certainty is null.

    Quote from mdenino

    Now, how do I mark this thread resolved?

    You don't. All you need to do is thank whomever helped you (which you have) and get back to work. :)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login