Click here to see a demonstration video.
The OCRetain 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.
Syntax
OCRetain
( target, default formula, key1, [ ... keyn ] )
The OCRetain function syntax has these parts:
| Argument | Description |
|---|---|
| target | Required. This argument identifies the data entry value to be retained. |
| default formula | 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. |
Remarks
To enter an OCRetain function, click on the Insert Function button (to the left of the Excel formula bar), select the Office Connector 2.x category, select OCRetain and click OK. You can then click on the Target cell, enter the formula and click on the Key cells.
Default Formula
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, "".
Example
Click here for examples.