Chart based on dynamic range

[RET79]

Hi.

I apologise for asking this question, this is something I should know but I can't seem to get it working.

I have two columns: date, fund value starting from D6:E6 downwards. I will be adding the data every know and then and want to base a line graph on it.

I don't know what to put as a reference in the chart wizard, I suspect it's something alone the lines of OFFSET(D6,0,0,count(d:d),1). Also what forumla would you put so that the x axis contains the dates.

thanks,

RET79

[Aladin Akyurek]

On 2002-05-06 13:36, RET79 wrote:
Hi.

I apologise for asking this question, this is something I should know but I can't seem to get it working.

I have two columns: date, fund value starting from D6:E6 downwards. I will be adding the data every know and then and want to base a line graph on it.

I don't know what to put as a reference in the chart wizard, I suspect it's something alone the lines of OFFSET(D6,0,0,count(d:d),1). Also what forumla would you put so that the x axis contains the dates.

thanks,

RET79

I'll assume that the worksheet that houses your data is called Data in what follows (Adjust the sheet name to suit).

Activate the option Insert|Name|Define.
Enter NumRecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=MATCH(9.99999999999999E+307,Data!$D:$D)

Activate Add. (Don't leave yet the Define Name window.)

Enter DataRecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=NumRecs-(ROW(Data!$D$6)-1)

Activate Add. (Don't leave yet the Define Name window.)

Enter Dates as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(Data!$D$6,0,0,DataRecs,1)

Activate Add. (Don't leave yet the Define Name window.)

Enter Funds as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(Data!$E$6,0,0,DataRecs,1)

Activate Add. (Don't leave yet the Define Name window.)

Enter DataBase as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(Data!$D$6,0,0,DataRecs,2)

Activate OK.

The above set up is such that dynamic name ranges includes also the labels, something that the Chart Wizard needs to know.

Aladin



[ This Message was edited by: aladin akyurek on 2002-05-06 14:45 ]

[RET79]

Aladin,

Thanks for your reply.

However I am still having difficulty getting this thing to work - I had the data labels in D6:E6 then the data itself from D7:E7 down which I didn't explain too well earlier.

Now, when I start the chart wizard, I assume that I put =Database in the data range place, could you perhaps confirm to me where the other ones go?

Sorry I am a complete novice when it comes to this stuff

RET79

[Aladin Akyurek]

On 2002-05-06 14:21, RET79 wrote:
Aladin,

Thanks for your reply.

However I am still having difficulty getting this thing to work - I had the data labels in D6:E6 then the data itself from D7:E7 down which I didn't explain too well earlier.

Now, when I start the chart wizard, I assume that I put =Database in the data range place, could you perhaps confirm to me where the other ones go?

Sorry I am a complete novice when it comes to this stuff [img]/board/images/smiles/icon_frown.gif[/img]

RET79

=NumRecs-(ROW(Data!$D$6))

must be:

=NumRecs-(ROW(Data!$D$6)-1) [ now edited ]

Otherwise you'd lose the last data row.

Now, when I start the chart wizard, I assume that I put =Database in the data range place, could you perhaps confirm to me where the other ones go?

Depends on the type of chart that you want to make. I gave DataBase as value of Data Range.

Aladin

[RET79]

Thanks again Aladin.

I am having difficulty creating this dynamic chart - I want a line chart. In the datarange thing I have put Database. I tried putting =funds in the Values: and =dates in the X-axis labels place but it won't accept either

Do you get this problem??

Thanks again,

RET79

[Aladin Akyurek]

I am having difficulty creating this dynamic chart - I want a line chart. Do you get this problem??

Yeah, me too. I forgat all about that charts don't accepts name ranges (dynamic or not). Then I suddenly remembered Stephen Bullen's work on dynamic charts (brought to my attention by Kristel, many thanks for that).

The previous definitions cannot be used as such in charting, keep them around anyway. I have strongly the impression that the Pivot Chart Wizard is able to use defined name range DataBase as would the Pivot Report.

Bullen's Steps for a Dynamic Chart

Here is a sample data that appears in D6:E25.

{"Date","Fund";
37257,500;
37264,249;
37271,345;
37278,654;
37285,287;
37292,658;
37299,347;
37306,987;
37313,238;
37320,456;
37327,675;
37334,435;
37341,536;
37348,237;
37355,679;
37362,347;
37369,600;
37376,600;
37383,800}

E6 houses as you can see Fund.

(1)

In E6 (yes, E6) enter:

="Funds"&TEXT(NOW(),"")

Bullen's explanation: "This formula is required to force the chart to update each time the sheet is recalculated. It is referenced by the chart as the series name."

I'll still assume that the worksheet that houses the data is called Data in what follows (Adjust the sheet name to suit).

(2)

Activate the option Insert|Name|Define.
Enter NumRecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=MATCH(9.99999999999999E+307,Data!$D:$D)

Activate Add. (Don't leave yet the Define Name window.)

(3)

Enter DRows as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=NumRecs-(ROW(Data!$D$6))

Activate Add. (Don't leave yet the Define Name window.)

(4)

Enter Cht_X_Values as name literally in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(Data!$D$6,1,0,DRows,1)

Activate Add. (Don't leave yet the Define Name window.)

(5)

Enter Cht_Y_Values as name literally in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(Data!$E$6,1,0,DRows,1)

Activate OK.

(6)

Activate the Chart Wizard.

(7)

Enter as Data range on the Data Range tab in the Source Data window:

=Data!$D$6:$E$25 [ The initial data range ]

Check Columns for Series in.

(8)

On the Seies tab:

Series box will show: Funds

keep it.

Enter for Name:

=Data!$E$6

Enter for Values:

=Cht_Y_Values

Enter for Category (X) axis labels:

=Data!Cht_X_Values [ Added the sheet name at Edit ]

You're done. You have a Line Chart.

Add a date value to D and a Fund value to E and watch what happens to the chart.

Note. Don't ask -- I don't know how Bullen has figured out all this.

Aladin




[ This Message was edited by: Aladin Akyurek on 2002-05-06 17:42 ]

[ This Message was edited by: aladin akyurek on 2002-05-06 22:50 ]

[RET79]

Argghhhh, it won't accept =Cht_Y_Values
in the values box.

Do you get this problem? For which line chart does this apply?

RET79

[RET79]

OK, I have sorted the problem - just for the record remember to put =Data!Cht_X_Values not =Cht_X_Values

Aladin, thanks for your help, wow this is so handy now,

RET79

[Aladin Akyurek]

just for the record remember to put =Data!Cht_X_Values not =Cht_X_Values

Lost it at the Copy and Paste stage of a long night . Somehow Excel needs that at the initial stage, thereafter prefixes those internal chart variables by the filename.

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-05-06 23:00 ]