Being able to “bind” the selected item in a Selector has really allowed Dashboard designers to do more with the magic of moving data behind the scenes. However, there are times when we want more control … like when we want to “unselect” a selection that has been made.
An example of this might be where we have 3 Combo Box selectors for Country, State and City and I want to express an “All Countries”, “All States” and “All Cities” option for a summary view of the data. The problem starts off with the issue that there are no “All Countries” or “All States” values in my data … so we will need to get a workaround for this.
The second issue is the problem of “unselecting” a previous selection. The behavior we want is for the State and City selectors to return to “All States” and “All Cities” whenever the Country changes.
Below is our sample data (with apologies to our Canadian friends for calling their Provinces states).
For my first selector, Country, I am going to add a formula in the column to the left of Country. In the header cell, I will type “All Countries”. Below that, in cell B7, I will add a formula that says, basically, “if the cell to the right is blank, then blank, else display the cell to the right”. In Excel, the formula looks like this: =IF(C7=””,””,C7). We can drag that formula all the way down our column with a few extra rows, in case our data grows.
| The Labels for the Country selector will look to cells B6:B25.We will set the insertion type to “Filtered Rows”.The Source data will be our data block, including the headers (since we included the header in our labels, we must include that row in our source data), but I am not including the formula column we added: Sheet1!$C$6:$E$25The Destination in this example will be Sheet1!$H$7:$J$20, which is the maximum number of rows for any one country, plus a few extra rows for growth. I highlighted this area in yellow, and added the words “All States” and “All Cities” in cells I6-J6. |  |
The final step for the Country selector is on the Behavior tab, to set the Selected Item to Label 1.
Now we are ready to set up our second selector, the State combo box.
| The Labels for the State selector will come from column I, Sheet1!$I$6:$I$20. This will include the “All States” label which I hard-coded to cell I6.The Insertion type will be Filtered Rows, and the Source Data will be just the cities in column J: Sheet1!$J$6:$J$20.The Destination cells I have set up in column L : Sheet1!$L$7:$L$13. I highlighted this area in yellow and added the words “All Cities” in cell L6. |  |
Now comes the magic part. On the Behavior tab, this time we are going to bind the “Insert Selected Item” to cell I4. (To see the behavior in action, you can add a spreadsheet component that displays this and surrounding cells).
When you preview the dashboard now, you will see that the Country selector starts with “All Countries” and the State selector is blank (for now). While in preview mode, select a country and notice that cell I4 displays the word “none” until you select a State from the drop down list. We are going to use this in a formula next. But for now, select a state, notice cell I4, then change the country and notice cell I4.
Also notice that the State selector becomes blank when you change countries. We will add a formula so that the selector returns to “All States” any time the country selector is changed.
Come out of preview mode and add a formula in cell I3: =IF(I4=”none”,”All States”,I4)
Now we can bind the Selected Item to this cell (I4) rather than having it say Label 1.
Almost finished! The final selector will be our City selector. The Labels for this selector will be in column L: Sheet1!$L$6:$L$13, making sure to include the “All Cities” in cell L6.
Set the Insertion Type to Row (unless you have repeating cities) and bind the Source Data to the entire city area (Sheet1!$L$6:$L$13) and bind the Destination to N7 (or additional rows if necessary).
The final touch will be to bind the “Insert Selected Item” to L4, add a similar formula to cell L3: =IF(L4=”none”, “All Cities”, L4)
And finally, bind the Selected Item/Item to the formula in cell L3.
Now, when you preview your dashboard, you will see that any time you change the country or the state selector will return to “All States” and any time the state is changed, the city selector will return to “All Cities”. Hope this helps solve some of your dashboard navigation issues.
Happy Dashboarding!
