to watch a video on using 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
(display value, refresh action, destination cell 1, source cell 1, [... destination cell n, source cell n]
function syntax has these parts:
||Required. This argument returns the value to be displayed in the cell containing the OCDrillDown function.
||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.
||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.
||One source cell is required for each destination cell. The value from the source cell will be copied into the preceding destination cell.
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:
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 and then .
- Click .
- Enter BackLink for Name.
- Change Scope to the active worksheet.
- Click .
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.