An Innovative way to use Data Validation feature of Excel

We all know what is data validation, and we know how useful it is. So, let's skip the introduction part. In today's write up I will show you a beautiful use case of data validation combined with a few lines of VBA code.

First, we need to identify the problem we are trying to resolve. We have an excel file with 3 sheets. This file has data about students of Grade 1, Grade 2, and Grade 3. We want to see the performance of the top 3 students in four subjects Maths, Eng, Science, and History. We want to construct a chart for each class that will show us the performance of the top 3 students (Data is already arranged in the top to bottom format in each sheet) in an excel dashboard. The traditional way to do this is to build each separate chart in each sheet and copy them to the dashboard sheet. So, there will be 3 graphs, and reading and understanding them will be easy. Now, if we have 12 Grades and four sections each, do you think 48 graphs in an excel dashboard will be a very useful representation?

However, there is a way around. This article is about that approach. We can solve this problem with a combination of data validation and VBA code. We will discuss that. Let me show you the data.

And this is what we are trying to achieve.

Now, normal data validation will not allow you to do this very easily. What we are trying to do here is to extract the data from each sheet to our master view sheet using the dropdown list. We will choose the source from the list, the data will appear and the graph will update using the information.

To do this, we need to create a Table of the content sheet first. How to do that automatically, I have explained that in a different article (Link). Then follow the steps below.

Step 1: Create a Table of Content Sheet

Step 2: Go to Master view sheet > Choose Cell A1 > Click on Data > Data Validation > Select List > Choose Source from Sheet Content column of TOC sheet

Step 3: Right Click on the Sheet Name (Master View) > View Codes > Type Codes > Close the Window > Choose from the Drop Down

The code is simple. We are copying data from the required sheet as per the drop-down, and then passing that value to a specific location in the Master View sheet. As simple as that.

We are using a worksheet change event to perform the task. Worksheet change event runs a macro when anything changes in the worksheet. A good explanation of the worksheet event is given in the following link.

Find below the final code.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
'The below line confirms that the code will run only if the change has been 'occurred in cell A1, this is a way of restricting the event to specific cell 'reference only

If Application.Intersect(Target, Range("A1")) Is Nothing Then GoTo ex
Dim str As String

'We are using vlookup to return the sheet name from TOC sheet. and storing it 'in a variable str

str = Application.WorksheetFunction.VLookup(Range("A1").Value, Sheets(2).Range("A:B"), 2, 0)

ThisWorkbook.Worksheets(str).Select
ActiveSheet.Range("A1:F5").Copy Sheets(1).Range("A3")
Sheets(1).Select
ex:
Application.ScreenUpdating = True

End Sub

Please guys you don't want me to explain how to build the chart right?

I hope this will be useful. Please leave your feedback below.