Sign Up for Training |
insightsoftware Company Site
Submit a Request
Become a Jet Insider
Give Feedback

Jet add-in and Excel Array Formulas


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.


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.

Was this article helpful?
0 out of 0 found this helpful