Results 1 to 11 of 11

Thread: Dynamic Chart

  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I got stuck... have done dynamic charts using named ranges with =OFFSET(....) many times. In the current situation, I've built the X and Y ranges in my sheet.

    Cell A1 e.g. has the string "A10:A20"
    Cell B1 e.g. has the string "B10:B10"

    I can put =Indirect(A1) in cell A2 and =indirect(B1) in cell B2 (to create ranges)

    I named cell A2 XRange and cell B2 YRange

    Now I thought that I could simply edit an XY-series in a graph to read: =SERIES(,XRange,YRange,1)

    Doesn't work... tried many variations (adding the sheet-name to the strings, e.g. A1 has "Sheet1!A10:A20") nothing works... I must be missing something; I don't need the OFFSET function, again, I have built the ranges to be plotted in my sheet...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't think there's a way to do that (but I'll be happy to be proved wrong!)

    You can use VBA code:

    Code:
    With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
      .XValues = Range(Range("A1"))
      .Values = Range(Range("B1"))
    End With
    You could have this run automatically by creating a Worksheet_Change event procedure in the worksheet module:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Range("A1:B1"), Target) Is Nothing Then
    	With Me.ChartObjects(1).Chart.SeriesCollection(1)
    	  .XValues = Range(Range("A1"))
    	  .Values = Range(Range("B1"))
    	End With
      End If
    End Sub

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am not sure why but INDIRECT acts differently than using OFFSET to create the range for a chart (it acts similarly in other circumstances).

    You can do what you want using OFFSET (though you must include the workbook in your series statement):

    =SERIES(,'File name.xls'!XRange,'File name.xls'!YRange,1)

    The problem seems to be that the chart requires not only a range, but the sheet that the range is on. It is in the offset since the initial range has a sheet for it. The sheet the range is on is not required for the INDIRECT to work, but is required for it to be used as a chart range.

    You could define names something like )change sheet name as desired:
    =INDIRECT("Sheet1!"&Sheet1!$B$1)
    =INDIRECT("Sheet1!"&Sheet1!$B$1)

    I tried having Excel get the sheetname (extracting it from Get.documents(76)) but the indirect did not seem to work (in the chart) with anything but it explicitly listed as text.

    It can be done by creating the full text in EXCEL and then using the INDIRECT with the sheet and range. if you create a name:
    Sheetname
    defined as:
    ="'"&MID(GET.DOCUMENT(76),FIND("]",GET.DOCUMENT(76))+1,50)&"'!"

    And then in A2 enter:
    =Sheetname&A1
    and in B2:
    =Sheetname&B1

    Then define the names Xrange and Yrange as:
    =Indirect($A$2)
    =Indirect($B$2)

    You can use the series as listed above (with the workbook name)
    Steve

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy

    [quote name='sdckapr' post='784061' date='10-Jul-2009 22:46']I am not sure why but INDIRECT acts differently than using OFFSET to create the range for a chart (it acts similarly in other circumstances).

    You can do what you want using OFFSET (though you must include the workbook in your series statement):

    =SERIES(,'File name.xls'!XRange,'File name.xls'!YRange,1)

    The problem seems to be that the chart requires not only a range, but the sheet that the range is on. It is in the offset since the initial range has a sheet for it. The sheet the range is on is not required for the INDIRECT to work, but is required for it to be used as a chart range.

    You could define names something like )change sheet name as desired:
    =INDIRECT("Sheet1!"&Sheet1!$B$1)
    =INDIRECT("Sheet1!"&Sheet1!$B$1)

    I tried having Excel get the sheetname (extracting it from Get.documents(76)) but the indirect did not seem to work (in the chart) with anything but it explicitly listed as text.

    It can be done by creating the full text in EXCEL and then using the INDIRECT with the sheet and range. if you create a name:
    Sheetname
    defined as:
    ="'"&MID(GET.DOCUMENT(76),FIND("]",GET.DOCUMENT(76))+1,50)&"'!"

    And then in A2 enter:
    =Sheetname&A1
    and in B2:
    =Sheetname&B1

    Then define the names Xrange and Yrange as:
    =Indirect($A$2)
    =Indirect($B$2)

    You can use the series as listed above (with the workbook name)
    Steve[/quote]

    Thanks but I still cannot get this to work... First of all, the get.document() doesn't work but I already had the same using =CELL("Filename").

    The rest I've tried again (and again) but without success. If you were able to get this working; maybe you can post your test sheet so I can see how you did it? Let's call the workbook "WorkBook", the sheet with the graph "Graph" and the sheet with the data "Data". I'd like to 'make' the ranges used by the graph on the "Graph" sheet (so the formulas refer to the "Data" sheet).

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you attach a simple example file, I will set it up...

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='784130' date='11-Jul-2009 15:47']If you attach a simple example file, I will set it up...[/quote]

    Here you go...
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'll leave it to Steve to set up the chart with defined names.

    The attached version demonstrates the approach I suggested (you must enable macros).
    Attached Files Attached Files

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    And here you go...
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='784149' date='11-Jul-2009 19:23']I'll leave it to Steve to set up the chart with defined names.

    The attached version demonstrates the approach I suggested (you must enable macros).[/quote]

    Hans, that works, thanks.

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='sdckapr' post='784163' date='12-Jul-2009 00:33']And here you go...[/quote]

    And this works too, thanks.

    Last question (although I can live with how things are now): the series is defined as =SERIES(,Workbook_edit.xls!xRange,Workbook_edit.xl s!yRange,1). The workbookname (Workbook_edit) seems fixed. I played a bit myself using your setup to get it to also allow this name to be automatically added, that didn't work however. Any final suggestions?

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    [quote name='ErikJan' post='784232' date='12-Jul-2009 13:50']And this works too, thanks.

    Last question (although I can live with how things are now): the series is defined as =SERIES(,Workbook_edit.xls!xRange,Workbook_edit.xl s!yRange,1). The workbookname (Workbook_edit) seems fixed. I played a bit myself using your setup to get it to also allow this name to be automatically added, that didn't work however. Any final suggestions?[/quote]

    The workbook name is not "fixed". It is the name of the workbook which can be changed manually or via code. Once set, the name in the series will adjust itself.

    I think the answer to your original question, having the line:

    =Series(,xRange,yRange,1)

    remains that I think it can not be done. When using named ranges in the series, the name must be prefaced by the workbook name. Also the named ranges (xRange and yRange) must include an explicit sheet name as well (which is why indirect does not always work but while OFFSET works more often. But once setup, the ranges are dynamic, whether using OFFSET or INDIRECT and will adjust as the workbook name is saved.

    Steve

Posting Permissions

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