Hi guys,
Please bear with me, there is some background to this problem.
I produce reports for approx 30 sales people. The main one is a waterfall graph to show how their sales portfolio is doing financially. For those that haven't come across them before, I have linked to them before in a previous thread of mine and I find them very useful.
This particular one shows:
Prev Month total -> New sales in -> Existing business increase/decrease -> Current month total
So you start with the PM total, add the sales in, add (or subtract) the existing business and you end up with the CM total.
The main thing to consider here is that different sales people have different sized portfolios, some have very slow moving, large PFs, some have very rapidly expanding ones, so the numbers involved here are very different.
When producing these graphs in the past, I created one Excel file per salesperson, so with this, I could manually adjust the scale on the graph to suit the start and end figures, making the items in betwen much more readable. If you imagine a start of 1,000,000, new sales of 100,000, existing of -50,000 leaving a CM total of 1,050,000 then you can see that if the scale started at 0, the two outer columns would be very tall and the two inner columns very squashed, so I used to adjust the scale "minimum" to approx 900,000 to make the two middle columns more readable.
Now, I have been asked to produce an all-in-one report, where based upon drop down lists, you can choose the salesperson you want and the table and graph (based on hidden data on another sheet) are updated. I have got this far, but my problem is how "readable" the columns are.
Q. Is there a way of changing the scale on the graph depending on the data going into it, preferably the value of the Prev Month Total?
I suspect that some VB coding will have to be done, but I was hoping to avoid that.
My apologies for the long-windedness of this post, I just wanted you to have some background on what I was doing here.
Many thanks in advance,
Mooski.
Please bear with me, there is some background to this problem.
I produce reports for approx 30 sales people. The main one is a waterfall graph to show how their sales portfolio is doing financially. For those that haven't come across them before, I have linked to them before in a previous thread of mine and I find them very useful.
This particular one shows:
Prev Month total -> New sales in -> Existing business increase/decrease -> Current month total
So you start with the PM total, add the sales in, add (or subtract) the existing business and you end up with the CM total.
The main thing to consider here is that different sales people have different sized portfolios, some have very slow moving, large PFs, some have very rapidly expanding ones, so the numbers involved here are very different.
When producing these graphs in the past, I created one Excel file per salesperson, so with this, I could manually adjust the scale on the graph to suit the start and end figures, making the items in betwen much more readable. If you imagine a start of 1,000,000, new sales of 100,000, existing of -50,000 leaving a CM total of 1,050,000 then you can see that if the scale started at 0, the two outer columns would be very tall and the two inner columns very squashed, so I used to adjust the scale "minimum" to approx 900,000 to make the two middle columns more readable.
Now, I have been asked to produce an all-in-one report, where based upon drop down lists, you can choose the salesperson you want and the table and graph (based on hidden data on another sheet) are updated. I have got this far, but my problem is how "readable" the columns are.
Q. Is there a way of changing the scale on the graph depending on the data going into it, preferably the value of the Prev Month Total?
I suspect that some VB coding will have to be done, but I was hoping to avoid that.
My apologies for the long-windedness of this post, I just wanted you to have some background on what I was doing here.
Many thanks in advance,
Mooski.