• 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.

Dynamic chart with swimlaned and indicator for current period

A

Anna Conda

New Member
Hi,

I do have to maintain a pretty big spreadsheet with approx 200 charts. The charts are simple line or bar charts. In addition green/yellow/red boxes indicate ok/not ok areas for the data values. Those boxes are done at the moment with graphic shapes. As well a vertical line is indicating the current period of time (done just by a graphical line).

Every month/period I have to adjust the time for the period as well as taking care of changing requirements for the colored boxes, which takes a lot of time and is the source of many errors.

I was playing with the charts to see if all 3 information could be automated: (1) line/bar charts showing the periodical data, (2) swim lanes indicating the good/bad areas and (3) an indicator for the current period. With the use of the secondary axis I can do either #2 or #3 but not both :-(

Challenge to the audience: is there a way to fully automate such a chart without manual intervention, just by adjusting the numbers?

I would very much apprechiate any suggestions!

PS: I wanted to upload a picture of the chart, but it says "an error occured". Will try again a bit later
 
Hi ,

What is required is a workbook with data for at least one chart , and the chart itself.

It may be possible to do everything without recourse to VBA , or some amount if VBA might be required ; is this acceptable ?

Narayan
 
Here is a sample workbook incl the two chart options I tried so far. Both of them require manual intervention which I would like to avoid.
Of course a solution without VBA would be nice, but if this the only option I would still go for it. Thanks in advance for your help!
 

Attachments

Hi ,

You also need to specify what are the variables ?

What is it that will vary as far as the charts are concerned ?

You need not consider what will be the mechanism for implementing what you want ; you only need to specify which aspects of the data will vary so that this can be made configurable.

Narayan
 
The data itself will be updated with copy&paste from different input spreadsheets. Every period, the period will change by one (and hence the period indicator in the chart should move). The ranges of the swimlanes are different for each chart but somewhat more stable for a couple of periods.
I've attached another sample spreadsheet to this post.
 

Attachments

Hi ,

Since the periods repeat every year , should you not specify the year also , to define the exact range of data for the chart ?

Narayan
 
Could do so, but it's not a mist do, as everybody knows which year we're currently in. The period marker always points to the period from the actual year as well.
 
Hi ,

So can I assume that the year will always be the current year , and the period being referred to is in the current year ?

Narayan
 
Hi ,

I am not sure why you are talking of automating your chart ; what exactly is the manual work that you need to do on the chart ?

Anyway , I have not done much ; go through the chart in the uploaded file , and explain in detail what is the kind of automation you are looking for.

Narayan
 

Attachments

You must log in or register to reply here.
Back
Top