Easy for me I mean, I’m trying to avoid looking these things up in the 7 different books I have to try to decipher what is happening.
About a Year ago I took one of the Dashboards avail from the Chandoo site and dissected it and got a pretty good understanding of how it was all working, but then I ran out of time and I had re-built a version that was doing exactly what I needed it to do, but I never got the chance to finish really learning all of how it was doing what it was doing.
First I will say the Workbook is called
Interactive Sales Dashboard and is available here
Interactive Sales Chart using MS Excel [video] | Chandoo.org - Learn Microsoft Excel Online (I hope its Ok that I Linked to it)
anyway it really amazing all the things that it does but the part in having trouble with is the VBA Code
And on other doozie that I can for the life of me understand is the Formula that is being used to display the items on the Y axis of a chart
(meaning that this formulate displays the names of items in cells and the Chart is lined up over them instead of using the axis legend to display the items)
The Formula is in cell G21
=INDEX(lstProducts,COLUMNS($G$18:G18))
and it returns the word Biscuits from the range "lstProducts"
This continues in cell H21 and the formula is =INDEX(lstProducts,COLUMNS($G$18:H18)) and it returns Chocolates
why in so baffled by this is that fist my understanding of what I have read about the COLUMNS argument is that it should return a count of columns for the given range.
And Second why is it Hard referencing Cell $G$18 and why is it soft referencing H18 etc. there is nothing in those cells, nor are they named range or a place or value holder of any Kind.
Thank you to anyone who is willing to take the time to help me understand this a bit more.
About a Year ago I took one of the Dashboards avail from the Chandoo site and dissected it and got a pretty good understanding of how it was all working, but then I ran out of time and I had re-built a version that was doing exactly what I needed it to do, but I never got the chance to finish really learning all of how it was doing what it was doing.
First I will say the Workbook is called
Interactive Sales Dashboard and is available here
Interactive Sales Chart using MS Excel [video] | Chandoo.org - Learn Microsoft Excel Online (I hope its Ok that I Linked to it)
anyway it really amazing all the things that it does but the part in having trouble with is the VBA Code
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(ActiveCell, [plstYears]) Is Nothing Then
[valYearPicked] = ActiveCell.Value
ElseIf Not Application.Intersect(ActiveCell, [plstRegions]) Is Nothing Then
[valRegionPicked] = ActiveCell.Value
ElseIf Not Application.Intersect(ActiveCell, [plstProducts]) Is Nothing Then
[valProductPicked] = ActiveCell.Value
End If
End Sub
And on other doozie that I can for the life of me understand is the Formula that is being used to display the items on the Y axis of a chart
(meaning that this formulate displays the names of items in cells and the Chart is lined up over them instead of using the axis legend to display the items)
The Formula is in cell G21
=INDEX(lstProducts,COLUMNS($G$18:G18))
and it returns the word Biscuits from the range "lstProducts"
This continues in cell H21 and the formula is =INDEX(lstProducts,COLUMNS($G$18:H18)) and it returns Chocolates
why in so baffled by this is that fist my understanding of what I have read about the COLUMNS argument is that it should return a count of columns for the given range.
And Second why is it Hard referencing Cell $G$18 and why is it soft referencing H18 etc. there is nothing in those cells, nor are they named range or a place or value holder of any Kind.
Thank you to anyone who is willing to take the time to help me understand this a bit more.