I have a range of data with age in the first column and then 3 further columns which all show different cashflow scenarios. I want to show this on a line chart with the age on the horizontal axis and the 3 cashflow scenarios on the vertical axis. The challenge is although the data starts at, say age 65 and ends at, say age 75 I want to be able to show the chart starting and ending at ages that I determine. Ideally I would like to have a control box that has 'age start' and 'age end' and based on the ages that I put in those boxes the chart updates to reflect the data over that age period - for example I may have 'age start' as '68' and 'age end' as '73' the chart would just show the cashflow scenarios for between these ages.

I had previously attempted to do this as a macro by using CONCATENATE to create the formula for the 'chart data range' which I then copied from the formula bar and pasted onto chart wizard. The macro did not like this. Amateur I know!

Below picture is using a dummy data set to demonstrate what I am trying to do.

All help would be much appreciated - please ask any questions if I haven't been clear. This is my first post!


Excel problem.png