Originally Posted by Dewey
How do I create a chart that automatically updates itself?
Let's say I have two columns of information -- date and, I don't know, money spent on shoes. And then I want to chart a 30-day average for money spent on shoes. Every day I update the money-spent-on-shoes column as I change the zeros to $money$$. Is there a way to make the line chart to update itself every day? It should grow horizontally day after day.
There's an easy way... and a macro way. The easy way is just to keep the empty rows hidden. The chart will condense itself to only show the unhidden days. You will have to unhide the rows to do your updates, and then rehide the same rows (minus the new one that is no longer empty), and your chart will also update.
If you're okay with VBA it's not a huge chore. The reason you have to use VBA to do this seamlessly is that the formulas the dictate the ranges of a data series in a chart don't
accept functions (EDIT: But they DO accept named ranges!!!)
For instance, the following is not valid:
...even though I think it should be. Maybe Excel 2007 is different, but I haven't used it.