Dynamic Chart Ranges based on Drop Down List Selection

Orion19

Board Regular
Joined
Dec 18, 2017
Messages
56
Hi All!

I have two drop down menus consisting of dates using the Data Validation method. I'm trying to get the X axis to update based off of the input from those drop down menus. For example, if the user selects 11/1/17 and 12/1/17 the graph should update on the x-axis to reflect that range.

I can get this working by naming ranges for my dates (named "Dates") and frequency (named "Frequency") values. From there I name ranges using the following formula to define values on the x-axis:

=INDEX(Dates,MATCH(Home!$A$7,Dates,0)):INDEX(Dates,MATCH(Home!$A$9,Dates,0))

I set the formula above to equal a range name "chart_Dates". Then, while selecting data for the graph, I enter the following:

"Horizontal (Category) axis labels: =Home!chart_Dates"

I repeat the process for y-values by updating references accordingly.

When I do that manually it works just fine. However, when I try to record the process with the macro recorder none of my changes to the horizontal axis come up. I've entered the following code to change the horizontal axis programmatically, and the range is changed accordingly, but the graph doesn't respond to changes from the drop down menu.

Code:
cht.Chart.SeriesCollection(1).XValues = "Home!chart_Dates"

Ultimately, I'm trying to define the range for the x and y axis programmatically without breaking the charts ability to automatically update based on the dropdown menu dates. Here is a sample of my data set. Thank you in advance!!

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]11/1/17[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Dates[/TD]
[TD="align: center"]Frequency[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]12/1/17[/TD]
[TD][/TD]
[TD]11/1/17
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/2/17[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/3/17[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

A1 = Dropdown list with 11/1/17 selected
A2 = Dropdown list with 12/1/17 selected
Column C continues until all dates are listed
Column D continues and matches the frequency for dates in column C

In this example the graph should only show dates (x axis) and frequency (y axis) between 11/1/17 and 12/1/17. Ideas and suggestions are appreciate!
 

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)

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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