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?
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.
Please disregard anything in the above post. It may well have been edited without my consent, as has been the case with several posts and threads recently.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks