Solved

Help Creating a Dynamic Stacked Chart

Posted on 2016-08-16
8
67 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 31

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 33

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

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 18

Expert Comment

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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

630 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