Comments:
You indicate you want the Excel chart series to display data by refereeing to a cell where the data can be found.
Excel chart series require a range but there are several ways you can express that range. You can create a name for the range. Excel has global worksheet names and local worksheet names. You create a local worksheet name by typing the worksheet name followed by an exclamation point and then the name.
If you have named a cell you can express the range for the chart by using the named cell along with the Excel’s OFFSET function. For example if you have named cell A1 START in worksheet called INTC and you want to display the data in cell A2 to A10 in the chart series then you can edit the chart series formula as follows:
=SERIES(INTC!OFFSET(START,1,0):INTC!(OFFSET(START,9,0))
It still seems a lot easier to create a local name and refer to it in the chart series formula. If the range for the series is going to expand or shrink I suggest you create local dynamic ranges. This technique is explained in detail in Willow Tip 2000-3 and Tip 2002-6. Download the sample workbooks and see how the names are defined and referred to in the charts.
|
