Taking User Inputs from Combo-Box
It is imperative that the dashboard you make is highly interactive. The greater the degree of flexibility you provide to the users to change the views, the better your dashboard is (…well not always but for now, mostly). To make the dashboard interactive, one needs user inputs and one of the most common ways of taking inputs is through the form control combo-box.
You would have seen it in many dashboards and it looks like this:
It feeds in from a list saved in a range somewhere on the sheet (ideally the control sheet) or even a named range for that matter (say a list of companies, or drugs; ideally it should be a unique list). So when a user clicks the small, downward facing arrow on the extreme right of this box, he gets all the available options (in the example above a list of Pharma companies). Upon selection, the combo-box returns the serial number of the list item in a linked cell that can be specified by the user i.e. Lizor above returns 1, Ranofi returns 2, and Tamtor returns 3.
Firstly, let’s see how to create a combo-box in action. The image below briefly captures the steps involved.
Step 1: Go to Developer > Controls > Insert> Form Controls> Combo-Box
Step 2: Draw a combo-box
Crab tip: draw it big enough to accommodate the largest possible name in your list
Step 3: Right click on the combo-box you just drew and select ‘format control‘
Step 4: Go to Control in the form that pops up
Step 5: In the field ‘input range’ enter the address of the list which you want your combo-box to pick up [e.g. 'A1:A14']
Step 6: In the field ‘cell link’ enter the cell where you want the input box to return a value
[As discussed earlier, this will be a numeric value representing the position of the selected item in the list]
So we have the position number, but how do we get the actual value? Using an INDEX formula in a cell (click here to visit the section on INDEX). The INDEX formula would be of the form:
INDEX (Address of the list that feeds the combo-box, Address of the cell which receives the input from your combo-box or the cell-link, 1)
e.g. if the list were in the range ‘B3:B5′, and ‘cell link’ returned the value in ’A1′
INDEX (B3:B5, A1, 1)
This formula will return the company name in the example above. Now you can use this value to drive the rest of the values in your dashboard as it will change every time the selection in the combo-box changes.
Download this MS Excel fileto see a working example of how the drop-down works. Feel free to reach out to us at firstname.lastname@example.org in case you face any difficulties in this section and we would be happy to address your queries.