Background
When using Sage Office Connector or Liberty Reports to insert a query in your Excel worksheet, the software automatically checks the "Fill down formulas in columns adjacent to query" box found in the External Data Range Properties window. This check box tells Excel to automatically copy formulas found in columns adjacent to the query down to new rows when data is refreshed. This setting also causes Excel to remove formulas when rows are deleted if the query results include fewer records.
In prior versions of Excel (2003, 2007, 2010, 2013), if the query returns no records, the resulting range of data would consist of two blank rows. Any adjacent columns containing formulas would still reflect the original formulas for those columns.
Problem
We have become aware of an issue with Excel 2016 that leads to a different outcome when a query returns no records. In this scenario, the cells in the adjacent formula columns are cleared and their contents replaced with the column caption. Since the formulas have been removed, if the query is refreshed again and the query returns records, no formulas will fill down to new rows because the formulas are gone.
Recommendation
Microsoft has not yet provided a solution to fix the problem. If you have not yet upgraded to Excel 2016, our recommendation is to refrain from upgrading until the bug has been corrected. If you have already upgraded to Excel 2016 and downgrading is not an option, The most recent versions of all Event 1 Software products are designed to resolve the issues described above. If you are not eligible for an upgrade to the latest software version, Event 1 Software can provide you with a specialized add-in that emulates the desired query table behavior. The add-in can be downloaded through our website:
Microsoft Excel 2016 Query Bug Fix
If you would like assistance installing the Fix, you may contact our technical support department and one of our support representatives will be happy to help!
Please note that although this add-in preserves the adjacent formulas, it is not intended as a permanent solution.