Click here to watch a video on using the OCDrillDown function.
Purpose
The OCDrillDown function allows you to double-click a cell to navigate to a detail worksheet and refresh data on it based on keys provided by the OCDrillDown function.
Syntax
OCDrillDown(display value, refresh action, destination cell 1, source cell 1, [... destination cell n, source cell n])
The OCDrillDown function syntax has these parts:
Argument | Description |
---|---|
display value | Required. This argument returns the value to be displayed in the cell containing the OCDrillDown function. |
refresh action | Required. This argument specifies how data on the destination worksheet should be refreshed. Pass TRUE to refresh queries on the destination worksheet, pass FALSE to do nothing. Alternately, you can provide the name of a macro to execute upon drilling down. Note that in all cases, the key values will be copied from the source cells into the destination cells and the destination worksheet will be activated. |
destination cell | At least one destination cell is required. The value from the following source cell will be copied into the destination cell. The worksheet containing the first destination cell will be activated upon double-clicking the cell containing the OCDrillDown function. |
source cell | One source cell is required for each destination cell. The value from the source cell will be copied into the preceding destination cell. |
Recommended Practices
Visual Feedback
It is a good practice to advertise the ability to drill down by using consistent formatting that calls out the cells purpose. The convention used in Event 1 templates is to follow the display value with a right-facing chevron. If the cell returns a text value, then you can append a chevron using concatenation:
=OCDrillDown(C38,FALSE,Offset,A38+1) & "»"
For numeric values, you can apply formatting to accomplish the same end result. Here is an example of a custom format for a cell that returns a currency value:
_($*#,##0.00_)"»";_($*(#,#0.00)"»";_($*"-"??_)"»";_(@_)"»"
Add a Link to Navigate Back
Leveraging the visual feedback to drill down, you can provide a convenient link to return to the drill-down point by following these steps:
- Near the top of the destination worksheet, add the following text to a cell:
« Back
- Select the cell.
- Click Formulas and then Name Manager.
- Click New.
- Enter
BackLink
for Name. - Change Scope to the active worksheet.
- Click OK.
Remarks
The OCDrillDown function is not intended for use in conjunction with the OCRetain function. Drilling down to detail typically causes an entirely different set of data to be returned, thus preventing values from being retained.