|
As a follow-up to the March 2005 article titled Dynamic Charting Formulas in Excel, reader Brett Havekost asked “if your database includes areas that you may not want to show, how you can dynamically eliminate the series and data labels for those areas from showing up in your chart?” The screenshot below illustrates a chart and chart data source that will be used as the basis for this explanation.
In this case we have a total of six projects spanning a total range of eight days. The supervisor wants to be able to graphically isolate a view of a count of people (FTEs) needed for each day or range of days that are assigned to each project. Drop-down boxes have been set up to capture a start date and a range of days to show in the chart.
The best way to further explain how this chart works is to work through some examples. All of the examples assume that the supervisor will be viewing the chart and not necessarily the data source on the right side of the chart.
Example 1
On Jan. 2, the supervisor comes to you and asks for a look at the FTEs required for the projects active today and for the next three days. Rather than build a whole new chart, we use the combination of dynamic named ranges and a macro to automate the process. Set the drop-down box on the left side of chart to Jan. 2. Set the drop-down box on the right side to show 4 periods.
The chart legend shows that three projects are active in the time period Jan. 2 – 5. The blue bars show how many FTEs are assigned to project A, the red bars show how many FTEs are assigned to project B, and the green bars show how many FTEs are assigned to project C on a per day basis. On Jan. 2 five FTEs are required. On Jan. 3 one more FTE is required. On Jan. 4 and Jan. 5, the chart shows that we’re back to a total of five FTEs per day.
Example 2
The next day, Jan. 3, the supervisor asks you to update the chart again but continue to keep the periods showing through Jan. 5. Set the drop-down box on the left side of chart to Jan. 3. Set the drop-down box on the right side to show 3 periods.
The only change to the chart that this update has made is to drop the data associated with Jan. 3 out of the plot area.
Example 3
The next day, Jan. 4, the supervisor asks you to once again update the chart but now wants to see five days into the future. Set the drop-down box on the left side of chart to Jan. 4. Set the drop-down box on the right side to show 5 periods.
Project A spanned Jan. 2 and Jan. 3 and, as a result, is complete and drops off the chart. Several more days have been added and reveal the total FTEs assigned to those future projects.
Building the Chart
The chart is set up in a stacked column format using data that spans the range K2:R10.
A defined name containing a dynamic formula is assigned to each project.
The formulas assigned to each range name in the Define Name dialog box above are:
The key to eliminating the series and data labels for the areas that you don’t want to see is to hide the columns for those areas. You can automate the process of hiding the columns by adding a macro to the spreadsheet to do just that. A macro that hides columns appears below. It is written assuming the active sheet name is “Sheet1” and triggers any time either of the two drop-downs are changed.
Sub HideColumns()
Dim Rng As Range
Dim ThisWs As Worksheet
Dim NewRng As Range
Dim cOffset As Integer
Dim cHeight As Integer
Application.ScreenUpdating = False
cOffset = Sheets("Sheet1").Range("E21").Value
cHeight = Sheets("Sheet1").Range("E22").Value
Sheets("Sheet1").Range("M2:R2").EntireColumn.Hidden = False
Set ThisWs = Sheets("Sheet1")
For Each Rng In Sheets("Sheet1").Range("M2:R2")
Set NewRng = ThisWs.Cells(Rng.Row + cOffset - 1, _
Rng.Column).Resize(cHeight, 1)
If Application.CountA(NewRng) = 0 Then
'MsgBox NewRng.Address(False, False) & " is empty."
'MsgBox Rng.Value & " is empty."
Rng.EntireColumn.Hidden = True
End If
Set NewRng = Nothing
Next
End Sub
In the first article of this series, titled Dynamic Charting Formulas in Excel, the basics of creating a dynamic chart are discussed. This follow-up article further discusses how the use of defined range names and macros can make a chart very interactive.
References
These sites provide detailed examples of dynamic charts:
John Mansfield is a Financial Analyst for a large clinic and hospital group located in Scottsdale, Arizona. His healthcare background includes Management Accounting, High-Level Planning and Analysis, and Project work.
John hosts an educational web log called The Planning Deskbook that focuses primarily on Microsoft Excel. The web log includes a minor emphasis on healthcare analysis and general web related topics.
|
