You might have a need to chart data that is formula linked from its original source. For example, the data is generated in Columns A (Date) & B (Data), but you want to chart the data from Columns U & V. So, you would put this formula in cell U1 (=A1) copy it to Column V and fill down to whatever # of rows you needed. As the data is generated in A & B, you would then want to chart the data from U & V. In the following Figure is an example what U & V might look like.
Notice what the columns look like where no information has been added at the data source. The column containing the dates return at the bottom 01/00/1900, which is 0 with a date format. The column containing the data return at the bottom 0’s. An attempt to chart this data as is shown in the following Figure. = SERIES(Control!$V$1,Control!$U$2:$U$39,Control!$V$2:$V$39,1)
Clearly, the chart does not produce the desired effect. A method of charting just the “good” data is needed. In order to identify the end of the good data, this defined name formula looks at column V and finds the 1st position of a 0.
Actually, the formula for doing that would exclude the -2, but it is needed for sizing the subsequent OFFSET formulas.
Data Range= OFFSET(Control!$V:$V,1,,EndOfData,)
So, the defined names formulas can now be used in the chart series formula.
Note that the defined names are at the workbook level. The desired chart is shown below.
Hope this helps!