Last updated on July 8, 2024

Solve a business analyst exam using the following instruction. Included in this tutorial is (PART 1 OF 2) how to create a dropdown list and (PART 2 OF 2) how to populate a scorecard based on the dropdown list, and to create a clear button to delete populated data.

Watch Youtube video, Part 1.
Read about Part 1 here.

Exam Objective: Create an automated scorecard

Please watch the Business Analyst Exam Part 2 for the step-by-step instruction on how to include this code in an excel file.

1) Under Scorecard Sheet

This code should create the trigger once change is made in cell D11 of Scorecard sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False

If Target.Address = “$D$11” Then
Range(“D10”).Activate
ActiveCell.FormulaR1C1 = “=VLOOKUP(R[1]C,Scores!R[-8]C[-3]:R[3]C[4],2,FALSE)”

Range(“D12”).Activate
ActiveCell.FormulaR1C1 = “=VLOOKUP(R[-1]C,Scores!R[-10]C[-3]:R[1]C[4],3,FALSE)”
Selection.Copy

‘Actual
Range(“E15”).Activate
ActiveCell.FormulaR1C1 = “=VLOOKUP(R[-4]C[-1],Scores!R[-13]C[-4]:R[-2]C[3],5,FALSE)”

Range(“E16”).Activate
ActiveCell.FormulaR1C1 = “=VLOOKUP(R[-5]C[-1],Scores!R[-14]C[-4]:R[-3]C[3],4,FALSE)”

Range(“E17”).Activate
ActiveCell.FormulaR1C1 = “=VLOOKUP(R[-6]C[-1],Scores!R[-15]C[-4]:R[-4]C[4],8,FALSE)”

Range(“E18”).Activate
ActiveCell.FormulaR1C1 = “=VLOOKUP(R[-7]C[-1],Scores!R[-16]C[-4]:R[-5]C[4],6,FALSE)”

Range(“E19”).Activate
ActiveCell.FormulaR1C1 = “=VLOOKUP(R[-8]C[-1],Scores!R[-17]C[-4]:R[-6]C[4],7,FALSE)”

‘%TOgoal
Range(“G15”).Activate
ActiveCell.FormulaR1C1 = “=RC[-1]/RC[-2]”

Range(“G16”).Activate
ActiveCell.FormulaR1C1 = “=RC[-1]/RC[-2]”

Range(“G17”).Activate
ActiveCell.FormulaR1C1 = “=RC[-1]/RC[-2]”

Range(“G18”).Activate
ActiveCell.FormulaR1C1 = “=RC[-1]/RC[-2]”

Range(“G19”).Activate
ActiveCell.FormulaR1C1 = “=RC[-1]/RC[-2]”

‘Rating
Range(“h15”).Activate
ActiveCell.FormulaR1C1 = “=RC[-1]*RC[-5]”

Range(“h16”).Activate
ActiveCell.FormulaR1C1 = “=RC[-1]*RC[-5]”

Range(“h17”).Activate
ActiveCell.FormulaR1C1 = “=RC[-1]*RC[-5]”

Range(“h18”).Activate
ActiveCell.FormulaR1C1 = “=RC[-1]*RC[-5]”

Range(“h19”).Activate
ActiveCell.FormulaR1C1 = “=RC[-1]*RC[-5]”

Range(“D11”).Select

End If
End Sub

2. Clear button

This code should clear populated data in the range provided.

Private Sub CommandButton1_Click()
‘CLEARS all data in the scorecard
ThisWorkbook.Worksheets(“Scorecard”).Range(“D10,D12”).ClearContents
ThisWorkbook.Worksheets(“Scorecard”).Range(“E15:E19”).ClearContents
ThisWorkbook.Worksheets(“Scorecard”).Range(“G15:G19”).ClearContents
ThisWorkbook.Worksheets(“Scorecard”).Range(“H15:H19”).ClearContent
End Sub

Send us a question in the comment section below.

Hope you learned something.

Helpful videos:
• Remove Manually a Spreadsheet Password (Tagalog Tutorial)
• Math Operation Excluding Hidden Cells in Excel (English Tutorial)
• Business Analyst Exam Part 1 of 2.