Skip to content

CREPORTLIST

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.

Syntax

CREPORTLIST (SelectedValue, OptionsRange, AutoCalc)

Arguments

SelectedValue : string (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"

OptionsRange : string (required)

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 OptionsRange argument.

Sydney 1
Melbourne 2
Brisbane 3
Adelaide 4
Perth 5
Canberra 6
Darwin 7
Hobart 8

AutoCalc : boolean

Specify if you want the report to calculate automatically when the user selects a value in the list.

Default value: true

Usage Scenarios

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:

  • Yes
  • No
  • Maybe

Rather than allow, Y, y, yes, YES or Yes (for yes), the use of a list ensures data validation.

Examples

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:

Notes

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: VLOOKUP(Value(C8),C2:D4,2,0)

In normal operation, rows 1 to 7 would be hidden before publishing.

Extensions

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)

=IF($C$8="A",CREPORTLIST(1,$H$2:$I$3,TRUE),IF($C$8="B",CREPORTLIST(Z,K2:L3,FALSE),CREPORTLIST(AA,$N$2:$O$3,TRUE)))

Results in the following 3 possibilities when published:

“Yes” selected:

“No” selected:

“Maybe” selected:

Again, rows 1 to 7 and 10 to 12 would normally be hidden when publishing.