Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help Creating a Dynamic Stacked Chart

Posted on 2016-08-16
8
Medium Priority
?
73 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 33

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 18

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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
 
LVL 34

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 18

Accepted Solution

by:
xtermie earned 1600 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 34

Assisted Solution

by:Rob Henson
Rob Henson earned 400 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 18

Expert Comment

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

Featured Post

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.

Question has a verified solution.

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

New style of hardware planning for Microsoft Exchange server.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

782 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