I have a report I am scheduling to output Values Only Workbook, and to remove hidden columns, rows and sheets. In this report, Cell B1 has "Hide+?" in order to create conditional hide formulas.
The issue is that when I schedule the report, Jet outputs column B as visible, though it is hidden in the regular jet report. It appears Jet's scheduler isn't recognizing "Hide+?" as a hidden row?
I've also tried having B1 be Hide+? for rows, and A2 be Hide+? for columns, with cell B2 as "HIDE." Theoretically this would hide both column B, and row 2. For some reason, it does hide column B, but row 2 is now showing!
Date
Votes
2 comments
-
Jet Reports Historic Posts I have found this problem from the start with Jet - I'm guessing it's just a quirk of the scheduler and order of operations.
I'm assuming it works something like a filter - Excel is filtering the "HIDE" rows/columns out of the report - if you removed the HIDE+? row/column before actually removing the hidden rows - then you are removing the filter and everything would be unhidden. As a result, I just write my reports to anticipate that I will have the HIDE+? columns showing if I schedule them.
To minimize their impact, you can make them very narrow after your report design is complete - I've actually gotten into the habit of using a narrow column A and row 1 in almost ALL of my Excel documents - Jet reports or not. I find that I actually prefer that now. Of course you have to be sure that your IF statement won't result in an error once the report scheduler is complete, or you'll have the ugly error references showing.
My IF statements usually look something like this (assuming I'm testing to remove zero values in column H from report)
=IF(H3=0,"HIDE","")&NP("EVAL","+""""")
basically - if the test is false, it's returning an empty text value (""), and the NP(EVAL statement will cause the formula to be converted to VALUES once the scheduler runs. -
Jet Reports Historic Posts Thanks, good info and I like that formula!
We're creating a file that will be auto-uploading to another program in that program's template, so I can't have the extra columns. If anyone has a trick, I'd appreciate it. It'll be hard to build the hide/show criteria into the row creator in this case.
Please sign in to leave a comment.