Need some help understanding a piece of VBA Code and an Unrelated Formula Please

[Tuckejam]

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

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.

 

[Kemmuniemans]

The VBA code just checks where the active cell is and if it is in one of the ranges [plstYears],[plstRegions] or [plstProducts] it sets the picked value to whatever value your activecell currently is.

The formula Index just gives the value of lstProducts where Columns() gives back a the number of columns in the given range. So COLUMNS($G$18:G18) gives back 1 and COLUMNS($G$18:H18) gives back 2. Making the Index formula: =INDEX(lstProducts,1) making it return Biscuits as I think the first entry in the lstProducts is Biscuits. Same applies for the 2nd entry which is Chocolates.

The hard referencing of $G$18 and soft referencing of G18, H18 etc. is just so when you drag the formula to the right, it will keep the starting column, but increase the end column by 1 when dragged. So the count of columns will increase with every cell it is dragged further to the right. So if you drag the formula: =INDEX(lstProducts,COLUMNS($G$18:G18)) to the right, it will increase the count of which entry it has to pick. so in cell I21 it would give the 3rd entry of the lstProducts, in cell J21 the 4th etc.

 

[Tuckejam]

Thank You, that all makes sense except im still hung up on that formula. now i understand what its doing, i just dont understand why, it seems to me that it would have been easier to just link directly to the cells in that range, rather than use the index/column formula, I guess i was just thinking that there must be some added benifit to doing it that way, and probably it was just a matter of prefrence when the creater built the workbook.

Thanks for the help

 

[Kemmuniemans]

In this case it would have been just as easy to hard reference the cells as no dynamic name range is used.
If the lstProduct would have been a dynamic list dependant on the amount of values added, the formula could be useful, but in this state no real benefit is gotten out of it imo.