The LRDrillDown 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 LRDrillDown function.
Syntax
LRDrillDown(display value, refresh action, destination cell 1, source cell 1, [... destination cell n, source cell n])
The LRDrillDown function syntax has these parts:
Argument
Description
display value
Required. This argument returns the value to be displayed in the cell containing the LRDrillDown 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 LRDrillDown 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: =LRDrillDown(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 LRDrillDown function is not intended for use in conjunction with the LRKeep function. Drilling down to detail typically causes an entirely different set of data to be returned, thus preventing values from being retained.