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

tmed834

New Member
Joined
Nov 26, 2017
Messages
3
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).

33fbaef.jpg


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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.

uP28g6l.png


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
 
Last edited:
Upvote 0
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.

uP28g6l.png


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!
 
Upvote 0

Forum statistics

Threads
1,224,825
Messages
6,181,191
Members
453,021
Latest member
pingpong7117

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top