Hi ,
It would help if you could say where you find difficulty in understanding the tutorial.
Broadly , what is happening is :
1. When you make your selection of two items from the left side and the right side , the following event procedure is executed :
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Value = "" Then Exit Sub
If Not (Application.Intersect(ActiveCell, Range("rngSel1").Cells) Is Nothing) Then
Call setOption1
ElseIf Not (Application.Intersect(ActiveCell, Range("rngSel2").Cells) Is Nothing) Then
Call setOption2
End If
End Sub
The two procedures which are called are
setOption1 and
setOption2.
These two procedures are :
Code:
Sub setOption1()
Dim topRow As Integer
topRow = Range("rngSel1").Cells(1, 1).Row
[valOption1] = ActiveCell.Row() - topRow + 1
End Sub
Sub setOption2()
Dim topRow As Integer
topRow = Range("rngSel2").Cells(1, 1).Row
[valOption2] = ActiveCell.Row() - topRow + 1
End Sub
What these two procedures are doing is to set the named ranges
valOption1 and
valOption2.
The charts themselves are pre-created on the calcs tab , in the range B72 through I34 ; the dashboard contains a picture placeholder
Picture 4 , which has the formula
=selChart.
selChart is a named range , which has the following formula in its Refers To box :
=CHOOSE(
valChartToDisplay,calcs!$C$73:$H$81, calcs!$C$84:$H$92, calcs!$C$95:$H$103, calcs!$C$114:$H$122, calcs!$C$125:$H$133)
What this does , therefore , is that depending on the value ( between 1 and 5 ) of the named range
valChartToDisplay , it chooses which of the 5 charts will be displayed in the picture placeholder.
valChartToDisplay itself refers to =calcs!$H$4 ; this is the linked cell of the Drop down 4. When you change the selection in the drop down , the value in
valChartToDisplay changes in the range 1 through 5.
Thus , the 5 charts are based on the data in static columns D and E , G and H , J and K , M and N , P and Q in the ranges $D$10:$D$37 and $E$10:$E$37 , $G$10:$G$37 and $H$10:$H37 , and so on.
Thus the 5 charts themselves are absolutely static ; nothing in these charts changes except the data.
The data itself changes because of the formulae in the cells in rows 10 through 37 ; depending on the selection made in the left side and the right side ( within the named ranges
rngSel1 and
rngSel2 ) , the titles in row 9 on the
calcs tab change. Since the cells D9 , E9 , G9 , H9 ,... are used in the formulae , the data changes based on the selection made.
Drop down 4 decides which of the 5 charts will be displayed.
Narayan