Solved

Help Creating a Dynamic Stacked Chart

Posted on 2016-08-16
  • SSIS
  • MS Excel
  • MS Office
  • MS Applications
  • Visual Basic.NET
  • +3
8
50 Views
Last Modified: 2016-09-10
Hello EE,

Can you please help me create a stacked similar to the one in my sample data attached here.  My data is in columns 5-10, but it can grow to column 25.  I do not want the legend to show if there are no data in columns 11-25 as an example.

Thanks in advance for your help.
0
Comment
Question by:fb1990
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41759043
You forgot to attache the sample workbook.
0
 
LVL 1

Author Comment

by:fb1990
ID: 41759152
My apologies.  I have attached the file now.  You have been a great helper.
My-Sample2.xlsx
0
 
LVL 17

Expert Comment

by:xtermie
ID: 41762658
1.      Click the File tab and choose Options. In Excel 2007, click the Office button and then click Excel options. In Excel 2003, choose Options from the Tools menu and skip to #3.
2.      Choose Advanced in the left pane.
3.      In the Display options for this worksheet section, choose the appropriate sheet from the drop-down menu.
4.      Uncheck the Show a zero in cells that have zero value option

Read along here
http://www.techrepublic.com/article/how-to-suppress-0-values-in-an-excel-chart/
0
 
LVL 1

Author Comment

by:fb1990
ID: 41762868
This does not work for me.  I need a solution that will change the chart dynamically on refresh  because the data can grow or shrink.

Thanks for your help
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 32

Expert Comment

by:Rob Henson
ID: 41764946
If you hide the rows that have blanks they will not appear in the chart.

Initially The EM# references still appearred in the legend. Go to the Select Data window and click the Switch Rows and Columns twice, ie swap and then swap back.

Alternatively you could make the data range dynamic based on a count of entries in one of the data columns. If that sounds like a better approach than hiding rows, let me know and I will advise.

Thanks
Rob H

EDIT: Dynamic Range doesn't seem to be working for me, I am sure it is possible so maybe another EE member will be able to advise.
1
 
LVL 17

Accepted Solution

by:
xtermie earned 400 total points (awarded by participants)
ID: 41765111
Here is a macro that will do what Rob says...hide empty value rows.
That would do the trick I think :)
I have attached an image of your sample file after I run the macro I am attaching below.

Sub HideEmptyRows()
Dim r As Range
Dim wb As Workbook
Dim ws As Worksheet
Dim ccol1, ccol2, ccol3, ccol4
'Unhide everything
Set r = ActiveSheet.Columns("A:A")
r.EntireRow.Hidden = False
Set r = Nothing
'Hide rows where no values in B, C, D, E
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nFirstRow = r.Row
ccol1 = 2
ccol2 = 3
ccol3 = 4
ccol4 = 5
For n = nFirstRow To nLastRow
    MsgBox Cells(n, ccol1).Value
    If Cells(n, ccol1).Value = 0 And Cells(n, ccol2).Value = 0 And _
    Cells(n, ccol3).Value = 0 And Cells(n, ccol4).Value = 0 Then
                Rows(n).EntireRow.Hidden = True
    End If
    Next
End Sub

Open in new window

EE_Graph.PNG
0
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 100 total points (awarded by participants)
ID: 41765227
As per your other question:

https://www.experts-exchange.com/questions/28963464/Preventing-Null-values-from-plotting-in-Excel-Charts.html

Solved by Neeraj with the use of Range Names in the chart.

You could do the same with the named ranges but make them dynamic (if they weren't already in that previous solution).
1
 
LVL 17

Expert Comment

by:xtermie
ID: 41792473
Good points and code provided
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

My experience with Windows 10 over a one year period and suggestions for smooth operation
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

911 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