0

Need help with a show/hide formula based on subtotal cell

Bob Thorp

October 26, 2017 19:10

None

Follow

Is this the same JET community site that I was able to upload files to and get advice on my formuals?  

Anyway, in the file I am trying to show or hide rows based on the row (T59) now equal to zero and the subtotal row (T58) not equal to zero.  My issue is with row replication the subtotal row also moves so the show hide formula is not showing rows it should. How would I adjust my file/formula so that the subtotal row could be is reflected properly

  =IF(AND($B$25=2,AND($T59<>0,AND(T58<>0,OR(MINA(M59:N59)<1,MAXA(M59:N59)>1,OR(MINA(Q59:R59)<1,MAXA(Q59:R59)>1))))),"Show","Hide")

• Facebook
• Twitter
• LinkedIn

6 comments

• 0
  
  Harry Lewis October 27, 2017 00:33

  Hi Bob -

  Yes, you have found the Jet Reports Community site.  It has been modified, lately, and is now part of the entire Jet Reports Help Center which combines Support, Community, Knowledgebase, and online Help

  Thank for the great photo!

  Which rows are you wanting to show or hide based on the contents of T58 and T59?

  Could you also include a screen shot of the report in Design mode?

• 0
  
  Bob Thorp October 30, 2017 13:40

  Here is a screenshot of the report in design mode.  The formula issue is in B39, as the desire is to show row 39 if 1) the Actual in T39 <>0, and the actual in T38<>0, 2) the budget or variance >+/- 1

  

• 0
  
  Harry Lewis November 02, 2017 15:05

  Hi Bob -

  There's a couple of things that I'm not sure about, but here is how I interpreted what you said.

  You want to show line 39 if *both* T38 and T39 are not zero *OR* R39 is either less than -1 or greater than 1.

  I also assume that row 39 is a replicated or and that you want to compare each replication (row 40, 41, etc) against row 38.

  So, here is what I came up with for the conditional hide function:

  =IF(OR(AND(T39<>0,$T$38<>0),ABS(R39)>1),"","HIDE")

• 0
  
  Bob Thorp November 02, 2017 15:47

  Hi Harry,  Your formula looks good except it does not account for the replication of row 38-41.  With replication, the reference of the subtotal row needs to be applied to the next subtotal "grouping."   So in this screenshot the second group would reference T49 rather than T38.  Is there a way for the subtotal row refrerence to follow the replication grouping?

  

• 0
  
  Harry Lewis November 02, 2017 16:08

  Hi Bob -

  The way to handle that is with what is commonly referred to as a "workaround".  Let's look at a very simplistic example:

  Here I have a grouping report.  My main grouping is highlighted.

  Let's suppose that I want to hide all the sub-rows where the value is less than the value in the header.

  What I would do is copy the header value to another column on the same row and then copy that copy down so that it is on the same row as the sub-value I want to compare.  Something like this:
  

  

  I've copied the header value (in cell H6) to cell C6.  Then, I've copied that copy down to cell C7.  Since I want to compare the heading to the sub-value (which is in cell H7), I now have my two values on the same row.  This makes comparison very easy.

  Now, I introduce my conditional hide:

  

  When I run the report, any sub-row with a value less than the header value will be hidden:

  

• 0
  
  Bob Thorp November 08, 2017 18:53

  Thanks Harry!  Meant to get back to you sooner.  I used your workaround method by adding a column for the subtotal and copied it down for each row in the "group" and the Hide formula works great.  Thanks again for your guidance on the work around and cleaner formulas as well.  Bob T