Behavior of "Remove Hidden Rows, Columns and Sheets" with Values Only Workbook

Summary

This article explains the behavior of the Remove Hidden Rows, Columns, and Sheets (/r) function when used in combination with the Values Only Workbook (/m VALUES) option in Jet Reports scheduled tasks. It also outlines the limitations and provides guidance on how to avoid #REF errors caused by hidden references.

Issue

When using the /m VALUES and /r parameters together in a Jet Reports batch file, hidden rows, columns, and sheets are removed during workbook generation. If a visible cell contains a formula referencing a hidden cell, the reference becomes invalid once the hidden cell is removed, resulting in a #REF! error.

Example Scenario

• Column C is marked with the keyword Hide.

• Column M contains formulas referencing cells in column C (For example, =C5).

• When the report is generated with /r, column C is removed.

• Excel attempts to calculate the formula in column M, but the referenced cells no longer exist, resulting in #REF! errors.

Root Cause

Jet Reports removes hidden rows, columns, and sheets as instructed by the /r parameter. Excel then attempts to calculate formulas referencing those removed cells, which leads to #REF! errors. Jet Reports does not control Excel's automatic calculation behavior.

Workarounds

To avoid #REF! errors caused by removed hidden references, consider one of the following options:

1. Remove /r from the batch file

   This will preserve hidden rows, columns, and sheets, allowing formulas to calculate correctly.

2. Switch to UPDATE or PDF mode

   Use /m UPDATE or /m PDF instead of /m VALUES. These modes do not remove hidden elements and will retain the referenced cells.

3. Avoid using the Hide keyword on referenced cells

   If a cell is referenced elsewhere, do not hide it. Instead, use formatting or other methods to visually suppress it without removal.

Limitations

• There are no Jet or Excel functions that can prevent Excel from calculating a formula referencing a removed cell.

• NP("Eval") cannot resolve references to cells that no longer exist in the workbook.

Recommendation

There are no Jet or Excel functions that can prevent Excel from calculating a formula referencing a removed cell. The NP("Eval") function cannot resolve references to cells that no longer exist in the workbook.

Additional Notes

If you need to display values from hidden columns without showing formulas, consider copying the values to a visible column using Jet formulas before applying the Hide keyword.