Auto update and conditionally format a chart

**mrbenn** · 07-03-2018, 04:15 AM

The attached spreadsheet has me tearing what little hair I have left out! I am trying to get it to auto update the charts based on the tables in the first tab.

The issue is that I have to remove the oldest month and add the previous. When I do this I then have to go to the chart and change the "Select Data" to point it at the added line.

I also need the charts to auto format based on the conditions of whether the target is above or below the actual. I also need the Max one to format based on the figure of the previous month, Red if it is higher, Green if lower. At present I am doing these formatting changes manually but there must be an easier way!

Any help would be gratefully received!

Regards

Stephen

**dflak** · 07-03-2018, 04:12 PM

I don't know what you were doing to make the charts not follow the tables, but I made that process automatic. The New Dates macro finds the top date in the table (For example May 2017) and adds a year and a month to it to make it June 2018. It puts this date as a new row on the bottom of the table, and then deletes the old first row. For OverMax it "remembers" the old value since we'll need it for charting.

There is no change to the Purchase vs. Target chart. That's straightforward.

To get the conditional charting for the other charts, break the data up to two series: Over and Under. Check out the formulas in each of the tables. Plot these two series and format over to red and under to green. Then set the series overlap to 100%. I set the gap to 200 just because I liked the way that looked.

You can hide these helper columns but then you will have to got to the Chart, Select Data and tell the chart to display hidden data.

I leave it to you to clean up the charts and move them to the bar graphs sheet as well as moving the tables back to where they were. Don't forget that OverMax will be writing to a row above the table so leave space for that. This row can be hidden.

I also destroyed your data with my testing. Copy / Paste values only where you don't have formulas.