• 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 - both x and y axes

P

Powhatan Jack

New Member
I have a spreadsheet with weekly values in columns for teams in rows. I want to be able to select which teams and weeks to chart based ranges entered in four cells. I have gotten dynamic ranges to work as far as the number of teams (bars in the cluster on the chart) but cannot figure out how to make the number of weeks (clusters of bars) dynamic. I have been attempting to accomplish this with a single series with no success. I would like to handle all of this without VBA.
 
Hi Jack ,


Can you see if this is acceptable ?


http://speedy.sh/qKkhe/Example.xlsx


You will find that there are error messages which keep popping up each time there is a recalculation ; the reason for this is that I have defined 7 series , one for each team , and 7 legend entries.


If you are sure that you will at most have 4 series , then you can afford to delete the remaining 3 viz. Series_5 , Series_6 and Series_7 , and Legend_5 , Legend_6 and Legend_7. This will eliminate the error messages. Of course , you will continue to get the error messages if you select less than 4 teams.


Narayan
 
Narayan,


Although I certainly appreciate your work on this, I was hoping for something a little smoother. The error messages when not choosing a certain number of teams for the series is exactly what I was hoping to avoid. I had setup dynamic ranges for each series in the production sheet but I was hoping for a way to have the number of series be dynamic as well. Does this make sense?


Jack
 
Hi Jack ,


I think the only way to make the number of series dynamic is to use VBA , I am not sure. Since you are averse to using VBA , I don't know if there is a way to get what you want. Let us see if anyone else can contribute something better.


Narayan
 
Narayan,


I am only adverse to VBA because of the security aspect. Beside, I lot of what I see from Chandoo is formula based and I enjoy seeing the possibilities. Anyone else have any ideas?


Jack
 
Hi Jack

Did you think at the OFFSET function in combination with an INDEX?

It allows you to adapt the chart dynamically in columns and rows.

Excel help is very explicit and Google gives a lot of examples.


Regards


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