?
Solved

Excel Dynamic Chart Range

Posted on 2013-06-27
11
Medium Priority
?
544 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 28

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 28

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

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

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
 

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 28

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 28

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 2000 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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
I came across an unsolved Outlook issue and here is my solution.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

621 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