Chapter 5
Stylizing Your Data
Section 5
Enhancing Sparklines using Named Ranges
Normal 0 false false false EN-IN X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin-top:0cm; mso-para-margin-right:0cm; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-fareast-language:EN-US;} Sparkline source ranges remain static unlike normal and PivotCharts in Excel. In order to reflect the selected time period in our dashboard, we need to use named ranges and VBA code to make them dynamic. - Create a named range for both the Sparkline chart range and the Sparkline data range (using a dynamic OFFSET function) - Record a macro of assigning the Sparkline charts to the named range for the Sparkline data - Create a subroutine on the dashboard worksheet object in VBA that calls the macro every time a change is made on the worksheet (SubWorksheet_Change)