• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple charts to control month

Chitraruveshar

New Member
Hi,

Previously, I use to create many Dashboard files with 1 chart and change the data series of the graph with a range of months dynamically. Then set an offset formula for each X and Y axis.

But the attached file has multiple charts but control the same month. At the moment, I manually increasing the month range in the chart. Does this mean for each chart, as follows

1) I have to set an Offset formula in the Name Manager
2) then set control in the chart to display the monthly to dynamically control the chart by month range?

Wow!! If I were to set formula for each x and y axis, then, it's going to be a long list of name manager.

Please advise or is there a better way to set the formula.

Regards
Chitra
 

Attachments

  • ChartTestDat.xlsx
    206.4 KB · Views: 14
Last edited:
Yes it would be a long list of Defined Names, you can't use OFFSET, INDIRECT in the sourcedata field, or even in individual series fields.

However (to expand on excelent's suggestion), since your charts don't currently plot hidden rows, if you set the chart to plot all rows, but hide those rows you don't want plotted you'll get the correct charts.
Now, how to hide the rows? Well, you can hide the rows manually - dead easy. You might try autofilter - but you can't have more than one autofilter on a sheet… unless that is, you convert the ranges to Tables, then you can filter. You'd only need a handful of tables per sheet, say 3.

In the attached (your file), I've extended the ranges of only the leftmost charts on your Overall sheet. On the SRMonth sheet I have hidden some rows; in the top section I have manually hidden rows 12-18. This results in the top chart looking exactly as it did before with data plotted to May. I've converted the second section to a Table and filtered it on 'RaisedA' (column B) not to show blanks, so the second chart plots up to June. The third section I converted to a table and filtered it manually using the months in column A, so the last chart also plots until June. You choose how you want to hide rows.

So now you only have to unhide/unfilter, add the data, rehide/refilter in a relatively few places each month.

Does this help?

Another solution could involve macros, either to create Names on a once-only basis, or to update all the charts' ranges each month… significant work.
 

Attachments

  • chandoo24745ChartTestDat.xlsx
    210.9 KB · Views: 39
Back
Top