[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

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
0
JameMeck
Asked:
JameMeck
1 Solution
 
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
 
Rory ArchibaldCommented:
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
 
JameMeckAuthor Commented:
Thanks!
It is a perfect solution.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now