Excel 2010: Dynamic graph.

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.

Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
FWIW, I'd use two charts as I think it's too cluttered otherwise. Attached has two charts, both dynamic.
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

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
JameMeckAuthor Commented:
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.

JameMeckAuthor Commented:
It is a perfect solution.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.