Change the limits of the X axis of a chart based on a cell value

Hi there. I've got a line chart that has years on the X axis that go from 2013 to 2078. I don't always want to show the chart going to 2078 though. Instead, based on a value I enter in cell F5, I'd like the X axis to end at a certain year.

In column E, I have the years 2013 through 2078 going from row 14 to row 79. In columns F and G from row 14 to row 79, I have sets of values that make up the line chart (there are two lines, one made from data in column F and one made from data in column G). In cell E3, I have a place to enter a person's age. In cell I14, I have "=E3", and then in cell I15 I have the formula "=The cell above this one +1" so that if the person were 30 in 2013, they'd be 31 in 2014, 32 in 2015, and so on until cell I79 in which they are 95.

What I want to do is to make it so that if I enter "42" in cell F5 and then push a command button to trigger the macro, this will cause the last year on the graph to be 2025 because the person would be 42 in 2025. In addition to that, I'd want the graph to automatically remove all data labels on the chart and then add a data label to the last (furthest into the future) data point on each line. In this example, there would be a data label that had the data of cell F26 for the first line and a data label that had the data of cell G26 for the second line since row 26 is the row where the person is 42 and the year is 2025.

Any help would be greatly appreciated!

Hi,

Could you please upload your workbook for me to work with?

Thanks

Hi ajryan88,

Sorry, I forgot to mark this thread as solved. I kept trying it on my own until I got my solution down to all but one line, so I created a new topic for that problem since it was asking for something different than this one. The ultimate solution was that instead of manipulating the chart, I tried to hide the rows of data below the last row that I wanted. I just couldn't figure out how to say "below this row". Here's the topic: http://www.excelforum.com/excel-prog...w-of-cell.html

So if I wanted the chart to end when the person was 42, I'd just hide rows 27 and below and then unhide them as needed when a new age was entered.

Thanks though!