Creating a Double Axis Dynamic Chart with Four Tables and a Drop Down Selection

[tmed834]

Hello, this is my first post! Pleasure to join the forum.

So I am trying to create a double axis chart, one will be a bar chart and the other will be a line chart. I have four locations I want to do this for, but if I have the data for all four countries then the visuals will be way too crammed. Here is some of the data below:

[TABLE="width: 1251"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Australia Conversions[/TD]
[TD]Australia CPA[/TD]
[TD]Hong Kong Conversions[/TD]
[TD]Hong Kong CPA[/TD]
[TD]New Zealand Conversions[/TD]
[TD]New Zealand CPA[/TD]
[TD]SingaporeConversions[/TD]
[TD]Singapore CPA[/TD]
[/TR]
[TR]
[TD="align: right"]8/1/2009[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]27.259[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15.5675[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]23.4875[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]9.514285714[/TD]
[/TR]
[TR]
[TD="align: right"]8/2/2009[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]31.68181818[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]67.57[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]47.445[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]23.264[/TD]
[/TR]
</tbody>[/TABLE]


And here is the chart for just one of the tables (one table for each country).

Essentially, if I can have a drop down list above the legend, where I can choose either Australia, Singapore, Hong Kong or New Zealand, then someone viewing the chart can have all the data here and select accordingly.

Thanks, and apologies if I broke any rules in how I posted. I read the rules, but not entirely. I will fix the thread if there is something blatantly wrong.

 

[thisoldman]

I haven't seen your complete data set. From what you show in the one chart, you might see better separation of the blue bars and the red line if you uses a single 0–70 vertical scale.

A different, separate cell range from the original data range is used for the chart source data.

I plotted some random or dummy data in T5:AB66. The headers were in row 5, dates in column T, SG CPAs in column AB.

The chart source data was in B5:D66. Column B just duplicates the dates in column T.

I put a Data Validation dropdown box in G3. Settings were for Allow: List; Source: 1. Australia,2. Hong Kong,3. New Zealand,4. Singapore
The numbers in the Data Validation Source list allow us to copy the values from the original data block to the charted data block using a CHOOSE formula rather than an INDEX+MATCH, VLOOKUP, or nested IF formula.

The charted data:

<tbody>
[TD="class: xl64"][/TD]
[TD="class: xl64, align: center"] B
[/TD]
[TD="class: xl65, align: center"] C
[/TD]
[TD="class: xl65, width: 64, align: center"] D
[/TD]

[TD="class: xl64"] 5
[/TD]
[TD="class: xl64"]Date[/TD]
[TD="class: xl65"]NZ Conversions[/TD]
[TD="class: xl65, width: 64"]NZ CPA[/TD]

[TD="class: xl66, align: right"] 6
[/TD]
[TD="class: xl66, align: right"]08/01/2009[/TD]
[TD="class: xl67, align: right"]11[/TD]
[TD="class: xl67, align: right"]47.905[/TD]

[TD="class: xl66, align: right"] 7
[/TD]
[TD="class: xl66, align: right"]08/02/2009[/TD]
[TD="class: xl67, align: right"]11[/TD]
[TD="class: xl67, align: right"]31.708[/TD]

[TD="class: xl66, align: right"] 8
[/TD]
[TD="class: xl66, align: right"]08/03/2009[/TD]
[TD="class: xl67, align: right"]34[/TD]
[TD="class: xl67, align: right"]8.196[/TD]

[TD="class: xl66, align: right"] 9
[/TD]
[TD="class: xl66, align: right"]08/04/2009[/TD]
[TD="class: xl67, align: right"]12[/TD]
[TD="class: xl67, align: right"]8.153[/TD]

</tbody>

Formula in C5, copied to the right and downward:
=CHOOSE(LEFT($G$3),U5,W5,Y5,AA5)

My example file is available at https://www.dropbox.com/s/vfv615swlgvtkf3/dynamic_chart_for_tmed834.xlsx?dl=0

 

[tmed834]

I haven't seen your complete data set. From what you show in the one chart, you might see better separation of the blue bars and the red line if you uses a single 0–70 vertical scale.

A different, separate cell range from the original data range is used for the chart source data.

I plotted some random or dummy data in T5:AB66. The headers were in row 5, dates in column T, SG CPAs in column AB.

The chart source data was in B5:D66. Column B just duplicates the dates in column T.

I put a Data Validation dropdown box in G3. Settings were for Allow: List; Source: 1. Australia,2. Hong Kong,3. New Zealand,4. Singapore
The numbers in the Data Validation Source list allow us to copy the values from the original data block to the charted data block using a CHOOSE formula rather than an INDEX+MATCH, VLOOKUP, or nested IF formula.

The charted data:

<tbody>
[TD="class: xl64"][/TD]
[TD="class: xl64, align: center"] B
[/TD]
[TD="class: xl65, align: center"] C
[/TD]
[TD="class: xl65, width: 64, align: center"] D
[/TD]

[TD="class: xl64"] 5
[/TD]
[TD="class: xl64"]Date[/TD]
[TD="class: xl65"]NZ Conversions[/TD]
[TD="class: xl65, width: 64"]NZ CPA[/TD]

[TD="class: xl66, align: right"] 6
[/TD]
[TD="class: xl66, align: right"]08/01/2009[/TD]
[TD="class: xl67, align: right"]11[/TD]
[TD="class: xl67, align: right"]47.905[/TD]

[TD="class: xl66, align: right"] 7
[/TD]
[TD="class: xl66, align: right"]08/02/2009[/TD]
[TD="class: xl67, align: right"]11[/TD]
[TD="class: xl67, align: right"]31.708[/TD]

[TD="class: xl66, align: right"] 8
[/TD]
[TD="class: xl66, align: right"]08/03/2009[/TD]
[TD="class: xl67, align: right"]34[/TD]
[TD="class: xl67, align: right"]8.196[/TD]

[TD="class: xl66, align: right"] 9
[/TD]
[TD="class: xl66, align: right"]08/04/2009[/TD]
[TD="class: xl67, align: right"]12[/TD]
[TD="class: xl67, align: right"]8.153[/TD]

</tbody>

Formula in C5, copied to the right and downward:
=CHOOSE(LEFT($G$3),U5,W5,Y5,AA5)

My example file is available at https://www.dropbox.com/s/vfv615swlgvtkf3/dynamic_chart_for_tmed834.xlsx?dl=0

Amazing!!!!!!! This is exactly what I wanted. I never really used the data validation tool or the choose formula at my last job, so I need some more practice with those. But I will use your example and study it so I can replicate this in the future. But your explanation is great.

Thanks so much!

 

[thisoldman]

Thanks for the kind words. You made me smile for much of the day.

 

[tmed834]

Thanks for the kind words. You made me smile for much of the day.

Haha. No problem. Thanks again!!