Solved

Excel Dynamic Chart Range

Posted on 2013-06-27
11
468 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 26

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 26

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 26

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 26

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 26

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 80

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will show you how to use shortcut menus in the Access run-time environment.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

708 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

16 Experts available now in Live!

Get 1:1 Help Now