0 Replies Latest reply on Feb 24, 2020 7:55 PM by Jeo123

    Sumif can break adhoc functionality

    Jeo123

      I figured I'd share this one since it just took me a while to trace down the one cell in a file that was breaking adhoc refreshes for a user.

       

      F1 is just a generic HsDescription function(formula shown in F2).  I used view since it's going to be common enough for anyone to try.

       

      G1 is where the problem starts, but it's not apparent at first.  Notice that the first parameter references two columns.  It was unintentional on the users part, but as you can see, excel doesn't error out, just assumes the sum_range should have been two columns as well and adds 1 and 2..

      G1=SUMIF(A:B,C1,D:D)

       

      So excel doesn't care about the extra column in the source/missing column in the sum_range.  If I just have those two forumulas, there's no bug.  The problem occurs when I either combine the two formulas as shown in cell G/H4 or building them directly into one formula as shown in G/H5.

       

      So the direct formula to disable adhoc refreshes is =hsdescription("View#YTD")&SUMIF(A:B,C1,D:D).  Note: this works with any Hs function, I just used description for simplicity.

       

      Once I put either function into an excel file, adhoc refreshes are disable across any other excel file(not just other tabs in this file) until I delete the formula or close this file out.

       

      This was a real needle in a haystack to find so hopefully someone benefits from this knowledge.  I've opened an SR with oracle, but this is such an obscure thing it might not get attention for a while.