Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Chart based on dynamic range

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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 ]

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,026
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •