Automatic adjustment of Axis

[Koejns]

I have a question about automatic adjustment of the X-axis in a graph.

I have drawn a graph of NPV with a X-axis of time in years, which is 1 to 40. I have a input cell where I can set the Life expectancy of the project (e.g. 25).

Is it possible to have excel change the x-axis depending on the life expectancy of the project (so somewhere between 1 and 40, depending on the input)

I am working in excel 2003 (company hasn't upgraded yet) and I want to do this without VBA's or Macro's as this is the explicit wish of the company.

 

[kpark91]

I think you can go about this by using names.
Altho I haven't worked with constant x-axis,
I managed to create automatic adjustment of x-axis and y-axis using ranges as names.

Will it be just one cell that represents the life expentancy or will it be a range?

For example,
will it be just 25 in cell A1? or 1 to 25 in cell A1:A25?

 

[Koejns]

It is just the 25

 

[kpark91]

Hi, Koejns.
I'm not too sure how to do it with a single cell so I'll just tell you how to do it with the range.

1) Select the x-axis range (A1:A40 even though A26:A40 may not be populated, you want the upperbound range), define it as a name. So, let's name it "x".

2) Select the y-axis range (B1:B40), define it as a name. So let's name it "y".

3) Create a graph

4) Right click on the graph

5) Select "Select Data"

6) Add Series

7) In x range, Type "='yourfilename.xls'!x" (without the double quotation) and in y range, type "='yourfilename.xls'!y" (without the double quotation)

8) Done!

Note: it is essential that your names are set to workbook level, NOT any other levels. and also make sure you type in the FULL name of your excel workbook with extensions inside the single quotation mark.

Hope it helps.

 

[Koejns]

Does this also work when I have multiple tabs or should I than add the tab name?

 

[kpark91]

I think it works even when you have multiple tabs as long as the name's scope is set to workbook.

 

[Koejns]

I can't get it to work propperly for me, any other ideas?