The CREPORTLIST formula is used to create a drop-down menu in published reports with the values supplied in a Range (ie
OptionsRange), and is a useful way to pass parameter arguments on a web page, allowing interaction with a user.
In typical use, other cells in your sheet would reference the selected value in the list to determine what to display. If
true, selecting any new value will trigger a calculation just as if the user had performed a calculation themselves.
CREPORTLIST (SelectedValue, OptionsRange, AutoCalc)
Specify the key value to show as selected in the list to the user by default when the published report loads.
This should be the key of the value you want to show - in the Capital City example below, the argument would be
Specify the range of cells from which to gather the list of values to fill the dropdown.
This range should be a two dimensional range of display values and keys, with the values you want to display for the user to choose from in the list in the left column (values) and the values that are stored in the cell when selected in the right column (keys).
Example: Australian capital cities with a number key - to be referenced by the
Specify if you want the report to calculate automatically when the user selects a value in the list.
To obtain a required input (restricted in value) to those items in the list. For example, whether to proceed or not could be any of:
Rather than allow, Y, y, yes, YES or Yes (for yes), the use of a list ensures data validation.
The formula (entered in cell C8), has a default value to show on publishing of Yes, which corresponds to key A. It uses the potential list in range B2 to C2, and will automatically calculate.
The formula entered in cell F6 is a common Excel formula, and is used to display on the web the values selected. The formula entered in cell F6 is
Once published, clicking on the drop down list, the following will be presented (Note: the default selection is highlighted)
Selecting “Yes” results in:
Selecting “No” results in:
Selecting “Maybe” results in:
Numbers or characters can be used in the second column of
OptionsRange (ie both the key and value can be text or numbers)
When using numbers, ensure that all of the cell formats are consistent, eg using
$ formatting in one cell and non-
$ in another cell may cause the list to link to the cell incorrectly. Number formatting rather than text formatting can also cause lookup failure. If using a number, the Excel function would change to:
In normal operation, rows 1 to 7 would be hidden before publishing.
It is possible, through the use of other Excel functions, to extend the use of CREPORTLIST, and have a secondary list displayed which is dependent upon the results of the first list. Extending the earlier example to include the additional lists as shown in cells H2 to I3, (which will be used if first result is A), K2 to L3 (if first selection is B) and N2 to O3 (selection C) and the advanced Excel
IF formula (in cell C13)
Results in the following 3 possibilities when published:
Again, rows 1 to 7 and 10 to 12 would normally be hidden when publishing.