Search code examples
google-sheetsdashboard

Goggle Sheets Dashboard Comparison Chart feature

I don't know if this is possible, or how to tackle it.

I have a table with 9 columns the first is Name-Eng-Esp-Fra-Ger-Rus-Ukr-Chi-Hin

On the dashboard I would like to have viewer select the column Data comparison. I.E. I want to see a comparison chart for the German column and the Russian column. Then the chart now reflects a two bar graph comparing just those.

Everytime I think I know how approach it I get lost in the weeds...

English German French Chinese Ukrainian Hindi Spanish Russian
Please 5 7 1 2 8 2 9 6
Thank you 1 6 3 4 12 5 7 3
Good Day 3 3 9 8 2 4 12 2
Your Welcome 2 10 4 1 5 7 1 5
Solution

  • I believe I mocked up an example based off the data you provided here.

    Essentially, you want to create a dynamic data range based on user input. So say you have a drop down in B10 and you have your full data set (including column headers and row labels) in the range A1:I5. Initially B10 is empty, but if a user selects Chinese (which should identically match one of your column headers), you can grab the data corresponding to that row with the following formula: =iferror(index($B$2:$I$5,,match(B$10,$B$1:$I$1,0))) (with the formula ia B11).

    In case you are unfamiliar with some of the formulas (starting from the inside out):

    • match - just searches for the position of a particular item in a range (in this case searching for the text "Chinese" within the range of column headers from the full data set)
    • index - traditionally this grabs one item in a range according to a provided row and cell index, but if you skip the row argument and only provide a column argument, then it returns the entire column
    • iferror - simply returns a blank cell if there is an error (just for a nicer UI)

    If you connect the chart to the range that corresponds to the dynamically populated data then the chart will automatically update when the user makes changes. A couple notes:

    • The title will not update automatically, so you'll want to make that generic if you use it at all (the legend though will update accordingly)
    • In case the data is uneven, you'll want the data range on the chart to reflect the largest possible range of data so nothing gets accidentally clipped.