Trouble with graph formatting - Auto hiding columns based on empty cell condition

[nickmoore211]

Hi all,

I am trying to get a graph to auto size depending on a variable user defined x-axis value (I wanted to attached the spreadsheet here but can't see an option to do so). I will explain as best as I can.

e.g.

- In a summary tab. There are a number of variables the user can select which effects the calculators results. There is a graph which represents these results.

- The user must select a model duration (in years) and is restricted to selecting the following values: 10, 20, 25, 40, 50, 60

- If the user selects an x-axis value of 20, whilst the data is only calculated until year 20, the graphs x-axis does not adjust to suit (i.e. it shows data up to year 20 but the x-axis length is still up to 60years)

- I need the graph to adjust so that if 20 years is selected then the graph will automatically re-size to suit this data input.

- The graphs source data is located in an expenditure profile tab. Each column represents years 1-60 and the rows represent various cost info.

- If a model duration of 20 years is selected in the summary, then the data cells in the columns for years 21-60 in the expenditure profile tab are automatically left blank.

- The only way I have been able to manually get the graph to resize is to hide these columns (i.e. years 21-60) and then the graph x-axis does adjust to suit.

- Therefore I believe been able to auto hide the columns based on a blank cells should achieve this?

- So to finally get to the question - how do I do this? I have searched the forum and tried to use various macros that people have posted as a solution. None of these seem to work on my model although I am a complete novice with regards to macro's and could be doing something fundamentally wrong?

I would appreciate any help anybody is able to give me. Hopefully I have provided a clear picture of the problem.

Thank you in advance

Nick

Note:
Excel version - 2007

 

[Sal Paradise]

You can use the techniques here to "cheat":
http://peltiertech.com/WordPress/fake-line-chart-dummy-xy-series-for-x-axis/

Basically, you make new series for your labels, and re-adjust your data so that they are all %, rather than years. Then you have the axis automatically create labels (using labeled XY series) to make your X-axis markers.

So, for instance, you create the following XY series and their labels:
0%-0
20%-0
40%-0
60%-0
80%-0
100%-0

If the term is 10 years, then your labels read: 0,2,4,6,8,10

If the term is 30 years, then your labels read: 0,6,12,18,24,30

If the term is 60 years... (I think you get the point).

That way you never have to resize the axis (it's always 100%), you just need to change your axis labels.

This is cheating, but it works.