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

T

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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
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
thisoldman said:
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
Click to expand...

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
You must log in or register to reply here.

Similar threads

R
  • Question Question
Change table data based on multiple dependent drop-down selections
Replies
2
Views
1K
Beneindias
B
H
  • Question Question
Problem with Peltier Technique - stacked charts
Replies
2
Views
823
HeneryH
H
A
  • Question Question
Scatter Chart plotting row numbers from data sheet / or / not plotting negative values
Replies
0
Views
3K
AuntRuth
A
K
  • Question Question
Is it Possible for Excel to select Column Chart Data Source Dynamically??
Replies
1
Views
559
JazzSP8
JazzSP8
O
  • Question Question
Dynamic Chart Ranges based on Drop Down List Selection
Replies
0
Views
549
Orion19
O

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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