Updating multiple graph column references one column to the right each month

[mike_vr]

Hi all,

Started to Google and got frustrated, so thought I'd come to the source of all power, and check if anyone has an easier way to update multiple chart column references in one go?

Each month I perform a set of calculations in tables, which then drive some pivot tables which are then refreshed, and then those pivot tables feed yet another summary page which performs additional calculations based on user-selected scenarios (e.g. they can select from a drop down and see the effect of shifting behaviour by 50% and things like that).

The final output is then graphed and sent out to all dept. heads.

The problem I have is they want to see all monthly data as it is on the summary page, but only the previous 6 months in graph form.

The attached graphs are a very small subset of the overall spreadsheet (counted them out and there are 280 graphs across all divisions) that I MANUALLY update each month using Design \ Select Data \ Edit and select my new month range, and it has pushed me to the brink of madness...

So, is there a way to build these graphs better, or automate the summary page, so that for this month the column references are J:O (+ their row numbers obviously), but then next month I do some neat trick like a Find & Replace all the graph references for J to K and O to P?

Hope this makes sense (and hope to heaven there's a solution!), but please let me know if I need to clarify anything?

Thanks in advance,
Mike

[AliGW]

This thread from earlier today might provide some inspiration: https://www.excelforum.com/excel-cha...t-working.html

[mike_vr]

Hi Ali,

Thanks for the reply, although looking at the thread link provided I cannot see what the relation is to my question?

Maybe I'm missing something or it's the wrong link, please let me know?

Thanks,
Mike

[romperstomper]

I'd suggest either using dynamic named range for the chart sources, or set up an additional sheet for the chart data, and use formulas in that sheet to pick up the last 6 months. The formulas will be similar in either case, but the latter is probably a little easier to setup, maintain and troubleshoot, especially if you aren't familiar with named ranges. The former has the advantage that you don't need to duplicate any data. Both methods will require manual set up now, which will probably be a bit of a pain with 280 charts, but at least only needs doing once.