Solved

Help Creating a Dynamic Stacked Chart

Posted on 2016-08-16
  • SSIS
  • MS Excel
  • MS Office
  • MS Applications
  • Visual Basic.NET
  • +3
8
43 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 31

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 31

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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.

747 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

13 Experts available now in Live!

Get 1:1 Help Now