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.