Variable chart Source Data Y Values

[Brian Withun]

I can't seem to encode the Y-Values of a chart to have a variable
range. (in Excel 2000)
I have tried things like:

="INDIRECT( CONCATENATE( "Sheet1!$A$1:$A$", TEXT($B$1,"0") ) )"
where $B$1 = 20.

I had hoped this would produce (and evaluate) the formula "Sheet1$A
$1:$A$20".
Instead, I receive "That function is not valid".
Does anyone know of a way to do this?



The reason I ask is:
I have a data series which keeps track of monthly values and estimates
the future months' values. This is done using a formula in future
month's cells to calculate the estimate. As each month passes I
replace the formula with an actual value.

I have produced a chart which has one series (A1:A20) that shows all
the historical values.
I have another series (A1:A99) on the same chart which shows all the
values, including estimated ones.

These lines naturally overlap.
The A1:A20 range is a heavy bold line which sits atop the thin A1:A99
line.

The problem is that each month I have to go into the Chart Data Source
dialog and update the Y Values of my first series (A1:A20) to include
the latest month (e.g. to A1:A21)

I would either like to have these Y Values 'soft coded', or rethink
the way I draw this chart.




Brian Herbert Withun

 

[Guest]

I'd probably use Offset in a named range for this

Your offset equation will be.

=OFFSET(Sheet1!$A$1,0,0,Sheet1!$B$1,1)

It add the named range, do this

Insert -> Name -> Define

Enter your name
Enter the above offset equation

In the series for the Y axis, put this

Book1.xls!myName

Where Book1 is the workbook name and myName is your named range