Solved

Excel Dynamic Chart Range

Posted on 2013-06-27
11
484 Views
Last Modified: 2015-01-05
Hello,

I am struggling with creating a chart that will automatically update it's range in excel.  I have tried several solutions (defining names, using a table, etc.) but I run in to the same problem every time.  The point is for the data to be copy pasted in to the correct month on the "master" worksheet and have it automatically update the charts for all of the stores.  The problem with the dynamic charts I have tried so far is they include all of the blank cells as they are not truly blank (they have formulas to find the data from the master tab).  I need to either find a way to have the chart ignore those cells until they are filled with actual numbers, or use a macro that will add the next row of data to each chart.

Thank you so much for your help,
Mark
Example.xlsx
0
Comment
Question by:Mtl321
  • 5
  • 5
11 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39282558
0
 

Author Comment

by:Mtl321
ID: 39282580
Thanks for such a quick reply,

Yes I did look at those already, their problems are all slightly different.  There was someone who had a similar problem, but the solution gave them the same problem that I am having.

Thanks,
Mark
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39283626
Try the attached file.
1. Change chart type to Line with Markers.
2. Change the formula in the chart source from =Master!X to =IF(Master!X=0,NA(),Master!X)
2. Automatically update the range using the Worksheet_Change event.
Example.xlsm
0
 

Author Comment

by:Mtl321
ID: 39286235
I do not see the “Worksheet_Change event” should it be under macros?
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39287242
Open the VBE (Visual Basic Editor) Alt+F11. See this for details http://www.excel-vba.com/vba-prog-1-1-editor.htm
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Mtl321
ID: 39287594
I am currently on a mac, would that make a difference?  I can gain access to a PC if necessary (and the final code I need is for a PC).
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39287674
No idea, never used a mac.
0
 

Author Comment

by:Mtl321
ID: 39302480
I can not get the macro to run (I see it) it just opens up a new window that says "macros" and is asking for a name when I try to run it.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39304353
The macro will run automatically every time a change is made to the worksheet. In other words just copy the code to your worksheet and forget about it, it will do the heavy lifting itself.
0
 

Author Comment

by:Mtl321
ID: 39359708
Hello again, I am so sorry for taking such a long time to reply I have been out of town.  I now see how the macro is working.  My problem is the chart has a ton of blank space to the right of the last month that the graph is plotting, I want to have the date extended for several years in advance, but the chart is updating the range to include every date on the sheet.  

I have attached the same sample worksheet showing the problem *(I only need to know how to do it on the "total" worksheet, and I can figure out how to copy it from there) .  Ideally I would like the chart range to only update to the line right before the "#N/A" rows start so the chart will not include the white space.  Thank you so much for all of your help!!!

Mark
updated-example.xlsm
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39399099
I replaced all the code in the worksheet codepanes with the following sub in Master worksheet code pane:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Sh As Worksheet
    Dim oChrt As ChartObject
    Dim sName As String, szSeries As String
    Dim rg As Range, rgLast As Range, rgSource As Range, rgY As Range

    For Each Sh In ActiveWorkbook.Worksheets
        If Sh.ChartObjects.Count > 0 Then
            For Each oChrt In Sh.ChartObjects
                szSeries = oChrt.Chart.SeriesCollection(1).Formula
                szSeries = Split(szSeries, ",")(2)
                sName = Split(szSeries, "!")(0)
                If Left(sName, 1) = "'" Then sName = Mid(sName, 2, Len(sName) - 2)
                Set rg = Worksheets(sName).Range(Split(szSeries, "!")(1))
                If rg.Rows.Count >= rg.Columns.Count Then
                    Set rg = Intersect(rg.EntireColumn, rg.CurrentRegion)
                Else
                    Set rg = Intersect(rg.EntireRow, rg.CurrentRegion)
                End If
                
                On Error Resume Next
                Set rgY = rg.SpecialCells(xlCellTypeFormulas, Value:=xlNumbers + xlTextValues)
                If rgY Is Nothing Then Set rgY = rg.SpecialCells(xlCellTypeConstants, Value:=xlNumbers + xlTextValues)
                On Error GoTo 0
    
                Set rgSource = rgY.CurrentRegion
                If rgY.Rows.Count >= rgY.Columns.Count Then
                    Set rgLast = Intersect(rgY.Cells(rgY.Cells.Count).EntireRow, rgSource)
                    Set rgSource = Worksheets(sName).Range(rgSource.Cells(1, 1), rgLast.Cells(rgSource.Columns.Count))
                Else
                    Set rgLast = Intersect(rgY.Cells(rgY.Cells.Count).EntireColumn, rgSource)
                    Set rgSource = Worksheets(sName).Range(rgSource.Cells(1, 1), rgLast.Cells(rgSource.Rows.Count))
                End If
                oChrt.Chart.SetSourceData rgSource
            Next oChrt
        End If
    Next

End Sub

Open in new window

updated-exampleQ28169891.xlsm
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

910 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

21 Experts available now in Live!

Get 1:1 Help Now