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.

 Excel-Dynamic-Graph.xlsx
JameMeckAsked:
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.
Excel-Dynamic-Graph--1-.xlsx
0
 
SiddharthRoutCommented:
Do you mean something like this?

Sample File attached.

Sid

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
LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Open in new window


Dynamic-Chart.xls
0
 
junholeeCommented:
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
 Excel-Dynamic-Graph.xlsx
0
 
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.

Thanks!
0
 
JameMeckAuthor Commented:
Thanks!
It is a perfect solution.
0
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.