To demonstrate additional flexibility that can be built into your dashboards, the downloadable exercise file (link below) contains a version that allows the user to select from a Data Validation dropdown list to select from a pre-programmed group of companies.
Based on the user’s selection, a calculated set of 1’s and 0’s is placed in the “Show” column. This, in turn, will cause the chart to redraw itself based on the targeted companies.
The trick to getting this to work is to have the group lists established off to the side…
…and a formula in the “Show” cells that adds a “1” to any company name listed in the user-selected group.
=IF($I$3="All", 1, SUMPRODUCT(($S$3:$U$8=A4) * ($I$3=$S$2:$U$2)))
Don’t get too distracted by the formula, just understand that if the user selects “All” from the dropdown list, every company will receive a “1” in their respective “Show” cell.
If the user selects a team color (ex: “Red”), every company listed under the “Red” team will receive a “1” while the remaining companies will receive a “0”.
Don’t worry so much how the SUMPRODUCT function works; it’s just a seed we’re planting in your brain to show what is possible.
For more information on SUMPRODUCT, you can find videos and posts on the SUMPRODUCT function here and the XelPlus YouTube channel.
Growing and Shrinking Tables
To allow the user to insert additional rows into our table and have the data preparation table “see” these new entries, we’ve converted the original data into a proper Excel Data Table.
If we add a new company named “Bold” with sales of 20,000, the new company appears when we have “All” selected from the dropdown.
If we add “Bold” to one of our group lists (ex: “Yellow”) and we select the “Yellow” team from the dropdown, “Bold” is included in the chart.