Column chart using Dynamic Data Table

Hi,

I'm using a table with dynamic data to populate a column/line chart.

The data is based on monthly targets achieved and forecasted. With each month, the information will update. The graph needs to have only the current month and future months to be displayed and I don't want to include the previous months information in the chart. The information in the chart automatically updates and loses the previous month's data. Unfortunately, the graph plots the blank data and has a blank entry on the graph. This means that the information I need starts in the middle of the chart and has a line that shoots up from 0 to the current month's value.

Is there any way that I can omit the month altogether?

The data source I am using is below:

Month Target for month* (examples)



October 100,123,669
November 125,154,586
December 150,185,503
January 175,216,420
February 200,247,337
March 225,278,254
April 250,309,171
May 275,340,089
June 300,371,006

As you can see, July August and September are not included. I want to be able to start the graph from October in the above example. In the following month, I want to use the same data but October Figures will not be required. How can I achieve this?

I hope this makes sense to you guys out there.

Thanks in advance to anyone who can figure this out for me.

Hi,

Thanks for that. I can see that this will work great. I assume that if I wish to add more data I just create a new named range, copy the same formula and adjust the column?

Also, I wanted to know if it is possible to change the chart by manually changing the current month. e.g. rather than it referencing to Today() can it reference to a date in a specific cell?

I'm still trying this out and will let you know how I get on.

Thanks again.

Have a look at this version. I've added a "Start Date" and "End Date" cell and the dynamic ranges will reference these.

Yes you can add more data sets to the chart and simply create a new dynamic range for each.
Note you only have to change part of the formula for the dynamic range.

The current one called "Targets" is:
=INDEX(Sheet1!$B:$B,MATCH(Sheet1!$G$2,Sheet1!$A:$A)):INDEX(Sheet1!$B:$B,MATCH(Sheet1!$G$3,Sheet1!$A:$A))

If you were to add another data set to column C you would change this to:
=INDEX(Sheet1!$C:$C,MATCH(Sheet1!$G$2,Sheet1!$A:$A)):INDEX(Sheet1!$C:$C,MATCH(Sheet1!$G$3,Sheet1!$A:$A))

Hi,

This is working when I paste my data into the workbook you created for me so I know that this is exactly what I want.
I'm now trying to get to grips with editing the series formula as I have never used this before.

Thanks again for your help. I'm sure I'll crack it soon enough.

It's easy enough to add new data sets.
Create the dynamic range, right click the chart and choose select data. Then click on Add and in the series box just put the sheet name then range name. It will look something like =SheetName!RangeName . Look at the ones already there to see what I mean.

Shout for help if you need more.

Hi BadlySpelledBuoy,

I've managed to get the data to update somewhat. However, I have still messed up somewhere.

I used your original formula using Today() and replacing it with $B$2 (where you can select the month manually).


Can you check this for me and see where I have gone wrong???


Thanks

There were a couple of things wrong, but you don't state the exact problem so I've fixed everything.

1) When you add a new data set you have to make sure the X-Axis range for that data set to the dynamic named range too. Sometimes Excel does this for you, sometimes not. in this case, it hadn't.

2) Your data table doesn't start in row one but has three blank rows above it. This means when the COUNTA part of your dynamic range formulas is trying to find the last row it's only counting the the row three before the end. This is easily fixed by adding +3 between the two closing parenthesis in the formulas.

If the issue you are having is neither of those above things, let me know

BSB.

Hi BSB,

I've finally got it.

Thank you so much for your help. This is going to make my report so much better.

Happy to help

Don't forget to mark the thread as SOLVED and feel free to shout it you need more help.

BSB.