Solved

Remove add series based on if there is data

Posted on 2014-01-07
2
184 Views
Last Modified: 2014-01-09
Dear Experts -

I am trying to graph data shown on the "Graphs" tab.   The data shown under each loan is driven by the start year put on the "Projections" tab which also drives other parts of the model.   Based on the year inserted the graph table will pull data from the outstanding debt tab.   Depending on the year chosen some of the loans may not have any data to show for the range displayed.   So I have two things I would like to address here.  (1) If there is no data to display is there a way to dynamically remove the series when the loan does not have any data to show but alternatvely if a range is selected were the loan does have data it is added to the graph, (2) how can a dynamicaly size the graph so that the graph on goes out as long as the last maturity of the loan?   I have attached the filing of what I am working with to hopefully shed some light on what I am asking for.  

Thanks in advance for your help!
Sample-Data.xlsm
0
Comment
Question by:Apex623
2 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39764209
You can hide series by hiding the cells they draw their data from. I did this with an event macro that runs when the Graphs worksheet is activated. This code must go in the code pane for the Graphs worksheet.
Private Sub Worksheet_Activate()
Dim cel As Range, rg As Range, rgA As Range, rg3 As Range
Set rgA = Columns(1).Find("Total", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
Set rg3 = Range("3:4").Find("Total", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
Set rg = Range(rgA.Offset(0, 1), Cells(rgA.Row, rg3.Column - 1))
For Each cel In rg.Cells
    cel.EntireColumn.Hidden = (cel.Value = 0)
Next
End Sub

Open in new window


I put the following formula in Graphs worksheet cell O30 to find the last year with bond revenue:
=INDEX(A5:A36,MATCH(0,L5:L36,0)-1)

I created a series of dynamic named ranges Loan1, Loan2, Loan3, etc. using Refers to formulas like:
=OFFSET(Years,0,MATCH(Graphs!$B$3,Graphs!$3:$3,0)-1)
In the above formula, the $B$3 part changes from $B$3 to $C$3, $D$3, etc for Loan1, Loan2, Loan3...

I then linked the stacked columns in the Debt Graph using series formulas like:
=SERIES(Graphs!$B$3,'Sample-DataQ28332928.xlsm'!Years,'Sample-DataQ28332928.xlsm'!Loan1,1)

The $B$3 and Loan1 parts change for each successive bar that is displayed.
Sample-DataQ28332928.xlsm
0
 
LVL 1

Author Closing Comment

by:Apex623
ID: 39769836
This gives me what I need!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

895 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