Today I was viewing one of the earliest Dashboards I created. This is one of the favorites of my colleagues.
In this post I will share the techniques and skills used in creating it with you guys. I rebuilt this dashboard from scratch using dummy figures.
Click here to Download the file
What is special about it? This “DASHBOARD” interacts when cells are selected.
So how does it work. Following are the tricks used
WORKSHEET EVENT AND APPLICATION.INTERSECT METHOD
Right click the worksheet tab and select view code. The following code is what does the trick
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Not Application.Intersect(ActiveCell, Range(“ITEMS”)) Is Nothing Then
Sheets(“Control”).Range(“ITEMSELECTED”) = ActiveCell
ElseIf Not Application.Intersect(ActiveCell, Range(“COMPANIES”)) Is Nothing Then
Sheets(“Control”).Range(“COMPANYSELECTED”) = ActiveCell
ElseIf Not Application.Intersect(ActiveCell, Range(“TRENDS”)) Is Nothing Then
Sheets(“Control”).Range(“TRENDSELECTED”) = ActiveCell
End If
End Sub
First I define namedranges where I want the users to make selections. In the above code you can see that there are 3 named ranges (ITEMS,COMPANIES,TRENDS)
Using Application.Intersect checks if the cell clicked by user falls within the above 3 ranges. If it does, it executes one of the line codes in blue above which gives us the VALUE of the selected CELL. Otherwise it does NOTHING
INDIRECT function with NAMED RANGES
Using INDIRECT(COMPANYSELECTED) returns the data for the company selected
A dynamic Named Range “ChartRange” is used as series Values which returns Monthly, Quarterly or Yearly Data based on User’s Choice
Another Dynamic Named Range “Chartlabels” is used to determine Chart Labels
POWER QUERY DIDN’T EXIST THOSE DAYS
Those days when I prepared this Dashboard, Power Query didn’t Exist
Today it will be very easy to achieve the same results in few minutes by consolidating the results with Power Query and using Slicers.
Slicers are really cool but sometimes they give me headaches with formatting and sorting. Those times I use the “Cell Click Interactions” because they give me more flexibility in terms of sorting and formatting.