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.
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!!
<tbody>
</tbody>
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!
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!!
| A | B | C | D | |
| 1 | 11/1/17 | Dates | Frequency | |
| 2 | 12/1/17 | 11/1/17 | 1 | |
| 3 | 11/2/17 | 0 | ||
| 4 | 11/3/17 | 5 |
<tbody>
</tbody>
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!
