graph series from reference?

[engrpetero]

A few months ago, several people at this site helped me get my hands around the INDEX statement and using it to refer to a cell range correctly. I'm now trying to use something similar for the 'Series Values' for a chart.

I've got data in cells E1:E1000 of a spreadsheet. I arbirtrarily choose sections of this range based on the values in two other cells (L1 and L2). I can use the INDEX function to return the corresponding values in the E1:E1000 range as follows

Cell Q1-->=INDEX(Results!E$1:E$1000,L$1)
Cell Q2-->=INDEX(Results!E$1:E$1000,L$2)

If L$1 holds the value '10' and L$2 holds the value of '45', then the above formulas will show the value of cell E10 in cell Q1 and the value of cell E45 in cell Q2.

What I want to do is use the cell range E10:E45 as a data series in a graph based on the above idea (that is, I'd like to be able to change the values in L$1 and L$2 in order to show different 'sections of data' for my graph line. I've tried several things but can't seem to make the graph data series recognize such a desire. Ideas?

[sweep]

Hi,

You need a named range.

Press CTRL F3 then new

enter a name for your range and then in the "refers to" dialogue:

=INDEX(Results!E$1:E$1000,L$1):INDEX(Results!E$1:E$1000,L$2)


Insert a chart, select data > edit series

Enter the name of your named range in the "values" dialog, preceded by a valid sheet name ie

=Results!MyNamedRange

The chart should then respond to the range as you change it.

Please see attached example for further info.

[engrpetero]

Thanks, Sweep!

I found this post elsewhere while searching the 'net.

http://stackoverflow.com/questions/1...nd-dynamically

However, your approach (along the same lines, in a way, as the link above) is the one I prefer.

Again - sincerely appreciated.