Charting Data With Formula Links In Excel By David Hager

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 1^st position of a 0.

EndOfData= MATCH(0,Control!$V:$V,0)-2

Actually, the formula for doing that would exclude the -2, but it is needed for sizing the subsequent OFFSET formulas.

DateRange= OFFSET(Control!$U:$U,1,,EndOfData,)

Data Range= OFFSET(Control!$V:$V,1,,EndOfData,)

So, the defined names formulas can now be used in the chart series formula.

=SERIES(Control!$V$1,ChartDraw.xlsm!DateRange,ChartDraw.xlsm!DataRange,1)

Note that the defined names are at the workbook level. The desired chart is shown below.

Hope this helps!