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.

dhpicture1

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)

dhpicture2

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.

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.

dhpicture3

Hope this helps!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s