I want to hide columns based on displayed activity in the column, but there appears to be an order in which the columns are hidden. On the attached report on the Report tab, I was using the formula, =IF(OR(MINA(I11:I35)<>0,MAXA(I11:I35)<>0),"Show","Hide") to hide a column if there was no activity in the column which worked fine, but when the selection on the options tab is selected to hide prior year activity, columns are still displayed even though no visible activity is shown. Therefore I switched to =IF(OR(AGGREGATE(4,5,G11:G35)<>0,AGGREGATE(5,5,G11:G35)<>0),"Show", "Hide") which appears like it should work as the columns with no activity are hidden but columns with hidden (row) activity are still displayed. When I "unhide" rows 1and 2, it shows "Hide" for the columns with only hidden activity, but the columns are still showing. Therefore I am assuming that the hide column function is processed prior to the hide row function. Is this a correct assumption? If yes, is there a work around for this situation? Thanks in advance for assistance with this issue. Bob T
2 comments
-
Jet Reports Historic Posts hi bt-twr
i don't know if there is an order, though i looked at your report.
But it is difficult, because i can't run that report caused by missing data.
is it pssible, that you upload the result of the report?
Or is this already the result, so that row 15 is shown, but should be hidden?
Have you tried it without the "lock" in A1 for the excelsheet? does it make a difference?
regards
jetsetter -
Jet Reports Historic Posts Okay, its been quite a while since I have been on this site, but I still haven't resolved the issue of columns not being hidden when they formula criteria is met.
In the attached file, I have removed "Lock" from A1 as suggested. The report is showing the results and I have unhidden rows 1 & 2 and columns a-c. Since L2 & O2 show "Hide" I would expect the columns to be hidden. When I unhide rows 136-146, there are amounts in columns L & O and L2 & O2 switch form "Hide" to "Show." Since the formula in those cells appears to work properly, I am back to the assumption that the hide column function (Row 2) is processed prior to the hide row function (Row 143). Any other thoughts on this? What about a work around?
Thanks,
BT-twr