Jet add-in and Excel Array Formulas

Overview

Excel array formulas ( those created with CTRL+SHIFT+ENTER, not to be confused with the Excel arrays returned by some Jet formulas ) do not work with a Jet Reports workbook with +VALUES or +LOCK if they are placed on a worksheet that has Jet formulas.

The behavior will be that the first time you run the report report, it will work.  Subsequent runs, however, will not work.

Explanation

When a report is run, all the formulas on pages containing +VALUES or +LOCK are copied to a secure location.  When the report is re-run (or returned to Design mode), the formulas are copied back to the report sheet and the CTRL+SHIFT+ENTER part of the formula  (what makes it an array formula) cannot occur as Excel does not provide a method to automatically detect or recreate this type of formula.

As a result, you cannot use Excel array formulas on sheets with Jet functions.  You should be able to put Excel array formulas on sheets that do not contain Jet functions, because formulas are not copied for those sheets.