Excel 2010: Dynamic graph.

Posted on 2011-04-20
Last Modified: 2012-05-11
Please check my file, I have 2 problems:

+ I want to make a dynamic graph, when I change date to 20 --> the graph only show data until 20, not all.

+ Why is the second axis, the value 0 is not same place with the first axis?

Please help me.

Question by:JameMeck
    LVL 30

    Expert Comment

    Do you mean something like this?

    Sample File attached.


    Code Used

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Cl As Long
        Dim aCell As Range, bCell As Range
        On Error GoTo Whoa
        Application.EnableEvents = False
        If Not Intersect(Target, Range("B1")) Is Nothing Then
            If Not Len(Trim(Range("B1").Value)) = 0 Then
                Set aCell = Range("B28:AF28")
                Set bCell = aCell.Find(What:=Target.Value, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
                If Not aCell Is Nothing Then
                    Cl = bCell.Column
                    ActiveSheet.ChartObjects("Chart 1").Activate
                    ActiveChart.SetSourceData Source:=Range("A28:" & Cells(32, Cl).Address)
                End If
            End If
        End If
        Application.EnableEvents = True
        Exit Sub
        MsgBox Err.Description
        Resume LetsContinue
    End Sub

    Open in new window

    LVL 2

    Expert Comment

    Hi JameMeck,
    First fit Seconary y axis ratio to 1st axis.
    So write minimum 2nd y axis 1.5.
    Second, use named range.
    For example, tgt
    ='Monitoring Graph'!$B$29:INDEX('Monitoring Graph'!$B$29:$AF$29,MATCH(0,'Monitoring Graph'!$B$30:$AF$30,0)-1)
    Use this name as chart series value range.
    Regards, junho

    Author Comment

    Thank Mr. SiddharthRout! I don't want to use Programming.

    To Mr. junholee: Could you please change to to dynamic value base on the date in Cell B1?
    When I change B1 to 23 --> the graph show data to 23.

    LVL 85

    Accepted Solution

    FWIW, I'd use two charts as I think it's too cluttered otherwise. Attached has two charts, both dynamic.

    Author Closing Comment

    It is a perfect solution.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Suggested Solutions

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now