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 autocalc is true, selecting any new value will trigger a calculation just as if the user had performed a calculation themselves.
CREPORTLIST (SelectedValue, OptionsRange, AutoCalc)
(Required) 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 “1” to show “Sydney”
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: Australia capital city Values alongside number Keys within a cell range - to be referenced by the OptionsRange argument
Specify if you want the report to calculate automatically when the user selects a value in the list
Default value: true
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:
- No or
Rather than allow, Y, y, yes, YES or Yes (for yes), the use of a list restricts the entry a user may make.
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 VLookup (C8,C2:D4,2,0 )
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:
For this function you can use numbers or characters in the second column (ie both the key and value can be text or numbers)
When you are using numbers, you must ensure that all of your cell formats are consistent , eg using $ formatting in one cell and non $ in another may cause the list to the link to the cell incorrectly. Number formatting versus text can also cause lookup failure. If using a number, the Excel function would change to:
VLookup ( Value ( C8 ),C2:D4,2,0 )
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.