Hi,
I am having a problem with my Jet reports losing references to cells that are hidden at runtime. They show the calculations fine while I am designing the report but once it is scheduled and sent I end up with loads of cells with #REF!.
If I leave the worksheet that contains the parameters/variables on visible the report runs fine but it looks messy when the report is exported with the options sheet visible.
I am assuming that your output file is deleting the hidden rows and columns via a BATCH operation. So, when you leave a formula in the cell, it can't find the reference.
You need to change your normal excel formulas into Jet formulas by adding a Jet function into them.
If your output is numerical, try adding
* NL(,NP("INTEGERS",1,1)) to the end of the formula (basically you're telling it to multiply the number by 1)
If your output is text, then add
&NP("EVAL","=""""") to the end of the formula
the numerical output will resolve to a number, while the text output will include the output, but it will still be a formula (example above might resolve to ="Vendor: " instead of Vendor)
Thank you Heather, that solved the problem.
Much appreciated.
I would also take a guess that you have the "remove hidden cell" turned on in the Scheduler. If you are ok with data still being there but hidden, that is the much better route. Don't remove hidden data from the report and the report will be able to reference while you don't see it.
Using volatile functions like TODAY will significantly impact report runtimes and should be avoided at all costs. (every time a cell is updated in the report, Excel will re-evaluate the TODAY function). Additionally, performing math on Jet functions will prevent batch functionality and also cause longer runtimes.
You can refer to these issues in this article https://support.jetglobal.com/hc/en-us/articles/218953198-Improving-Report-Performance
