Chart Macro & Update Source Data

[lawre2000]

Hello Excel Gurus, I have a data file that summarizes information for multiple years (2008-2030) which is summarized in a line chart. For the later years (2023-2030), there is no information currently and thus I do not want the 2023-2030 data to be graphed in my line chart. Is there a macro code to update the source data series values and Category (x) axis labels years based on the years that have data have in them?

For example, my source data chart has years 2008-2030 but currently I only have data for 2008-2023 in my chart. I want the chart to only graph the 2008-2023 data. It is important that the code be flexible to reflect changes in the chart as I may have information in the same chart at a future date that has data for 2008-2028 for example and I ideally it would recognize these "extra" years and update the chart to reflect those years as well. Many thanks in advance!

 

[earldunning]

Hello,
I am not an expert, and if I cant help maybe someone else will jump in. But I did something similar recently with weeks.

Let me ask this, in your data do you have a column that just has the year?
I made an extra column that just had the week and keyed my pivot table off that. I built the column as an if then so if there was no data in the other date field, it showed blank.


Like this: (D2 has the actual date in it).
=IF(ISBLANK(D2),"",YEAR(D2))

Or if you already have this, you could put something similar in the data that doesnt show the year if its beyond a certain date. Make a cell have the year you want to go to say 2023.
Say A1=2023 and the actual year for the line is in Column D
Then use:
=If(ISBLANK(D1),"",IF(D1>A1,"",D1)

This would then only show data that is within your year range you want. The pivot would only pickup that data since the other would be blanked out (or if it picks it up you can tell it not to use blanks). (you may have to re-order the years in the pivot the first time only)
Then you could just change cell A1 if you want to change the range. and refresh the data.

I am sure there are other ways. I look forward to hearing what others think.

 

[lawre2000]

Thanks for the reply. The only problem with the solution is you proposed is that chart will still look in those cells that are blank (cells that are blank because there is no data). When the chart looks at the blank cells, it will still reserve a spot for them on the chart even if though there is no data.

 

[earldunning]

If you use a pivot table to summarize the data, you have the option to tell it to skip blanks.
then make the chart of the pivot table.

But, everytime you input data you would still need to right click the table and hit refresh (simple 3 second thing, unless you had a macro automatically do it).

I see your other pt better now. You already have the chart done you just want it to automatically know when to stop.

You could use a range for the data source. The range would automatically change depending on where the data starts and stops.

I haven't done it before but I found some related info on it here:
http://www.mrexcel.com/forum/showthread.php?t=297916&highlight=chart+automatically+end

and here
http://www.mrexcel.com/forum/showthread.php?t=312514&highlight=chart+automatically+end

and here
http://www.mrexcel.com/forum/showthread.php?t=277829&highlight=chart+automatically+end

Hope that helps!