+ Reply to Thread
Results 1 to 8 of 8

Dynamically chart active row

  1. #1
    Patti
    Guest

    Dynamically chart active row

    I'm sure I saw a web page that showed how to create a dynamic chart that
    allowed you to move your cursor to any row and have the chart update with the
    data from that row. Does anyone know of a URL for something like this?

    Thanks!

    Patti

  2. #2
    Don Guillett
    Guest

    Re: Dynamically chart active row

    Here is one I use to chart a column from a double click event in sheet code.
    This is more complicated that you need. I must leave the office for the day
    but can help tomorrow.
    Modify to suit.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
    Boolean)
    If Target.Column > 1 And Target.Row = 3 Then
    [f1] = ActiveCell.Column - 1
    '[f2] = Replace(UCase(Cells(3, ActiveCell.Column)), "^", "") _
    '& " From " & [symbols!b2] & " To " & [symbols!c2]
    [F2] = Cells(3, ActiveCell.Column) & " From " & [symbols!b2] & " To " &
    [symbols!c2]
    Sheets("Chart").Select
    End If
    End Sub
    --
    Don Guillett
    SalesAid Software
    donaldb@281.com
    "Patti" <Patti@discussions.microsoft.com> wrote in message
    news:68BA7D41-6F75-4C02-AFA1-C165DB3274C3@microsoft.com...
    > I'm sure I saw a web page that showed how to create a dynamic chart that
    > allowed you to move your cursor to any row and have the chart update with

    the
    > data from that row. Does anyone know of a URL for something like this?
    >
    > Thanks!
    >
    > Patti




  3. #3
    Ed Ferrero
    Guest

    Re: Dynamically chart active row

    Hi Patti,

    Look for 'Chart Selector' here http://edferrero.m6.net/charting.html

    Ed Ferrero

    "Patti" <Patti@discussions.microsoft.com> wrote in message
    news:68BA7D41-6F75-4C02-AFA1-C165DB3274C3@microsoft.com...
    > I'm sure I saw a web page that showed how to create a dynamic chart that
    > allowed you to move your cursor to any row and have the chart update with
    > the
    > data from that row. Does anyone know of a URL for something like this?
    >
    > Thanks!
    >
    > Patti




  4. #4
    Patti
    Guest

    Re: Dynamically chart active row

    Thanks to both for replying.

    The chart selector reflects what I was looking for, but I don't see any
    instructions on how to create it. I see you are using a named range with
    offset, etc, but I'm not sure exactly how to duplicate it.

    Patti

    "Ed Ferrero" wrote:

    > Hi Patti,
    >
    > Look for 'Chart Selector' here http://edferrero.m6.net/charting.html
    >
    > Ed Ferrero
    >
    > "Patti" <Patti@discussions.microsoft.com> wrote in message
    > news:68BA7D41-6F75-4C02-AFA1-C165DB3274C3@microsoft.com...
    > > I'm sure I saw a web page that showed how to create a dynamic chart that
    > > allowed you to move your cursor to any row and have the chart update with
    > > the
    > > data from that row. Does anyone know of a URL for something like this?
    > >
    > > Thanks!
    > >
    > > Patti

    >
    >
    >


  5. #5
    Ed Ferrero
    Guest

    Re: Dynamically chart active row

    Hi Patti,

    The 'Chart Selector' runs VBA code on the SheetSelectionChange event. The
    code should have enough comments to make it understandable - basically, it
    looks for cells that are formatted in bold with a certain colour and assumes
    that these are chart headers.

    There are two ways to use this in your application;

    1) Easy way - paste your data in 'Chart Selector'
    Change the row and column headings to what you require, and paste your
    data into the worksheet.
    Save As... whatever you like.

    2) Copy the bits that make this work into a new workbook
    Open the VB Editor, copy the code in the Workbook pane to your workbook.
    Unhide both charts and copy across to your workbook.
    (There are two charts embedded in the worksheet - chtDim,and chtMeasure)
    Format a row and a column in Bold and with
    a grey background (interior.colorIndex = 15) - for chtDim
    or a blue background (interior.colorIndex = 37) - for chtMeasure
    Build the named range xAxis, which is a dynamic range to cover
    the header row, in my case it is
    =OFFSET(Sheet1!$B$7,0,0,1,COUNTA(Sheet1!$7:$7)-1)
    Change the number 7 to whatever your row is.

    Ed Ferrero

    "Patti" <Patti@discussions.microsoft.com> wrote in message
    news:24421427-B7C7-42BC-B38C-E46BF51B2131@microsoft.com...
    > Thanks to both for replying.
    >
    > The chart selector reflects what I was looking for, but I don't see any
    > instructions on how to create it. I see you are using a named range with
    > offset, etc, but I'm not sure exactly how to duplicate it.
    >
    > Patti
    >
    > "Ed Ferrero" wrote:
    >
    >> Hi Patti,
    >>
    >> Look for 'Chart Selector' here http://edferrero.m6.net/charting.html
    >>
    >> Ed Ferrero
    >>
    >> "Patti" <Patti@discussions.microsoft.com> wrote in message
    >> news:68BA7D41-6F75-4C02-AFA1-C165DB3274C3@microsoft.com...
    >> > I'm sure I saw a web page that showed how to create a dynamic chart
    >> > that
    >> > allowed you to move your cursor to any row and have the chart update
    >> > with
    >> > the
    >> > data from that row. Does anyone know of a URL for something like this?
    >> >
    >> > Thanks!
    >> >
    >> > Patti

    >>
    >>
    >>




  6. #6
    Jon Peltier
    Guest

    Re: Dynamically chart active row

    Patti -

    John Walkenbach (http://j-walk.com) covers it in the Interactive Charting section of
    his Excel Charts book. I believe it's essentially the same technique as Ed describes.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______

    Patti wrote:

    > I'm sure I saw a web page that showed how to create a dynamic chart that
    > allowed you to move your cursor to any row and have the chart update with the
    > data from that row. Does anyone know of a URL for something like this?
    >
    > Thanks!
    >
    > Patti



  7. #7
    Patti
    Guest

    Re: Dynamically chart active row

    Thanks for the help Ed!

    "Ed Ferrero" wrote:

    > Hi Patti,
    >
    > The 'Chart Selector' runs VBA code on the SheetSelectionChange event. The
    > code should have enough comments to make it understandable - basically, it
    > looks for cells that are formatted in bold with a certain colour and assumes
    > that these are chart headers.
    >
    > There are two ways to use this in your application;
    >
    > 1) Easy way - paste your data in 'Chart Selector'
    > Change the row and column headings to what you require, and paste your
    > data into the worksheet.
    > Save As... whatever you like.
    >
    > 2) Copy the bits that make this work into a new workbook
    > Open the VB Editor, copy the code in the Workbook pane to your workbook.
    > Unhide both charts and copy across to your workbook.
    > (There are two charts embedded in the worksheet - chtDim,and chtMeasure)
    > Format a row and a column in Bold and with
    > a grey background (interior.colorIndex = 15) - for chtDim
    > or a blue background (interior.colorIndex = 37) - for chtMeasure
    > Build the named range xAxis, which is a dynamic range to cover
    > the header row, in my case it is
    > =OFFSET(Sheet1!$B$7,0,0,1,COUNTA(Sheet1!$7:$7)-1)
    > Change the number 7 to whatever your row is.
    >
    > Ed Ferrero
    >
    > "Patti" <Patti@discussions.microsoft.com> wrote in message
    > news:24421427-B7C7-42BC-B38C-E46BF51B2131@microsoft.com...
    > > Thanks to both for replying.
    > >
    > > The chart selector reflects what I was looking for, but I don't see any
    > > instructions on how to create it. I see you are using a named range with
    > > offset, etc, but I'm not sure exactly how to duplicate it.
    > >
    > > Patti
    > >
    > > "Ed Ferrero" wrote:
    > >
    > >> Hi Patti,
    > >>
    > >> Look for 'Chart Selector' here http://edferrero.m6.net/charting.html
    > >>
    > >> Ed Ferrero
    > >>
    > >> "Patti" <Patti@discussions.microsoft.com> wrote in message
    > >> news:68BA7D41-6F75-4C02-AFA1-C165DB3274C3@microsoft.com...
    > >> > I'm sure I saw a web page that showed how to create a dynamic chart
    > >> > that
    > >> > allowed you to move your cursor to any row and have the chart update
    > >> > with
    > >> > the
    > >> > data from that row. Does anyone know of a URL for something like this?
    > >> >
    > >> > Thanks!
    > >> >
    > >> > Patti
    > >>
    > >>
    > >>

    >
    >
    >


  8. #8
    Tushar Mehta
    Guest

    Re: Dynamically chart active row

    A non-programmatic solution that redraws the graph with the recalculate
    key (F9 on a Wintel machine):

    Suppose the data are in rows 4 onwards, starting with column A. Also
    suppose the number of columns with data is *not* known *and* could vary
    from row to row.

    Select row 4. Create a named formula (Insert | Name > Define...)

    CurrRow =OFFSET(Sheet1!$A4,0,0,1,COUNTA(Sheet1!4:4))

    Note the use of both absolute and relative addresses in the formula.

    Now, create a chart using a named formula. If you don't know how see
    Names in Charts
    http://www.tushar-
    mehta.com/excel/newsgroups/dynamic_charts/names_in_charts.html

    Select any cell in any row with data. Press F9 and the chart will show
    the data in that row.

    --
    Regards,

    Tushar Mehta
    www.tushar-mehta.com
    Excel, PowerPoint, and VBA add-ins, tutorials
    Custom MS Office productivity solutions

    In article <10D85E35-6AD9-40D9-8D64-33E1BD0656C8@microsoft.com>,
    Patti@discussions.microsoft.com says...
    > Thanks for the help Ed!
    >
    > "Ed Ferrero" wrote:
    >
    > > Hi Patti,
    > >
    > > The 'Chart Selector' runs VBA code on the SheetSelectionChange event. The
    > > code should have enough comments to make it understandable - basically, it
    > > looks for cells that are formatted in bold with a certain colour and assumes
    > > that these are chart headers.
    > >
    > > There are two ways to use this in your application;
    > >
    > > 1) Easy way - paste your data in 'Chart Selector'
    > > Change the row and column headings to what you require, and paste your
    > > data into the worksheet.
    > > Save As... whatever you like.
    > >
    > > 2) Copy the bits that make this work into a new workbook
    > > Open the VB Editor, copy the code in the Workbook pane to your workbook.
    > > Unhide both charts and copy across to your workbook.
    > > (There are two charts embedded in the worksheet - chtDim,and chtMeasure)
    > > Format a row and a column in Bold and with
    > > a grey background (interior.colorIndex = 15) - for chtDim
    > > or a blue background (interior.colorIndex = 37) - for chtMeasure
    > > Build the named range xAxis, which is a dynamic range to cover
    > > the header row, in my case it is
    > > =OFFSET(Sheet1!$B$7,0,0,1,COUNTA(Sheet1!$7:$7)-1)
    > > Change the number 7 to whatever your row is.
    > >
    > > Ed Ferrero
    > >
    > > "Patti" <Patti@discussions.microsoft.com> wrote in message
    > > news:24421427-B7C7-42BC-B38C-E46BF51B2131@microsoft.com...
    > > > Thanks to both for replying.
    > > >
    > > > The chart selector reflects what I was looking for, but I don't see any
    > > > instructions on how to create it. I see you are using a named range with
    > > > offset, etc, but I'm not sure exactly how to duplicate it.
    > > >
    > > > Patti
    > > >
    > > > "Ed Ferrero" wrote:
    > > >
    > > >> Hi Patti,
    > > >>
    > > >> Look for 'Chart Selector' here http://edferrero.m6.net/charting.html
    > > >>
    > > >> Ed Ferrero
    > > >>
    > > >> "Patti" <Patti@discussions.microsoft.com> wrote in message
    > > >> news:68BA7D41-6F75-4C02-AFA1-C165DB3274C3@microsoft.com...
    > > >> > I'm sure I saw a web page that showed how to create a dynamic chart
    > > >> > that
    > > >> > allowed you to move your cursor to any row and have the chart update
    > > >> > with
    > > >> > the
    > > >> > data from that row. Does anyone know of a URL for something like this?
    > > >> >
    > > >> > Thanks!
    > > >> >
    > > >> > Patti
    > > >>
    > > >>
    > > >>

    > >
    > >
    > >

    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1