Chart based on dynamic range

RET79

Well-known Member
Joined
Mar 19, 2002
Messages
526
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0
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
 
Upvote 0
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 /board/images/smiles/icon_frown.gif

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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top