to see a demonstration video.
function keeps the values in a data entry column on the correct rows when the adjacent query is refreshed, while also enabling adjacent formulas to fill down.
, default formula
, [ ... keyn
function syntax has these parts:
||Required. This argument identifies the data entry value to be retained.
||Required. This argument specifies the function to be used to initialize data entry cells in new rows.
|key 1 ... key n
||Required. The combination of key values uniquely identifies the row where the data entry value belongs.
To enter an OCRetain
function, click on the
button (to the left of the Excel formula bar
), select the Office Connector 2.x
category, select OCRetain
. You can then click on the Target
cell, enter the formula and click on the Key
The default formula
argument is used to initialize cells in the data entry column when new rows appear in the query. The most common values are a zero for numeric columns and a blank for text columns, but you can enter any valid formula.
The reason for requiring a formula versus a static value has to do with Excel’s behavior for filling down adjacent functions. Excel fills down formulas in columns from left to right for as long as it keeps encountering formulas. If you type static text in a column, then formulas in the columns to the right will not fill down when the query is refreshed.
To overcome this limitation, Office Connector inserts default formula
into the data entry column prior to refreshing the data, and then restores the data entry values to the correct rows after the refresh completes. Any new rows that appear will still contain default formula
Office Connector expects default formula
to match the format used in the Excel Formula Bar, starting with the equal sign. Since default formula
is a text argument, it must be enclosed in double quotes. A typical default formula for a numeric data entry column is "=0"
Text formulas can be tricky because of technicalities involving double quotes inside of double quotes. For this reason, Office Connector supports a convenient shortcut to represent a formula for entering blank text: omit the equal sign and simply enter two double quotes, ""