Case Study 1 – Multi Choice Question-Answer Model in Excel

NOTE : There are many interlinked articles for separating basic lesson from this main article. View those links if you want to learn Basics.

Hello Excel Modelers,

Yes you heard it right. I am sure after going through this article thoroughly, you will gain a great insight on implementing various SIMPLE (simple if you know and complex if you haven’t ever used) Excel Functions in building the Excel Model. As I always say if you really want to be Excel Guru, don’t just memorize Excel Shortcuts but learn various functions of Excel and implement it in real life. Thats what we will be covering in this article.

First Let me tell you the background. I am undergoing the ISA course(ICAI Post qualification course) currently. The exam for which is a MCQ pattern and passing is as high as 75%. And obviously the success in this type of exam greatly depend on how many mock exam you have undergone. So we have been given a Question Bank of 3000 MCQ in Excel. Now the problem is the Model to solve question was very user Unfriendly so I decided to redesign the model and hence written this article to enhance your knowledge as well.

I have narrated the exact process that I followed in developing the model (thought process as well as corresponding) actions.  It took around 2.5 hours in developing a full proof model and testing it rigorously. I am sure once you go though the below process, you will learn many new Excel Functions and will come to know how to implement the same in real life situations.

To simplify the process/presentation, I will bifurcate the entire process in several Parts.

PART I  – Download the files

Original File – DISA_Ques bank-3000 ques

Final File – DISA_Ques bank-3000 ques – New Model

PART II  – Weakness in the Original File

If you see the original File and start using it, you may found following problems in ease of use:

  1. Need to Enter answer  manually in B11.
  2. Need to key a lengthy Key combination to move to “Next” (Ctrl + Shift + N) and “Previous” (Ctrl + Shift + P)
  3. No Provision to jump say from Question 67 to Question 2087 – you need to press 2020 times “Next” (Ctrl + Shift + N)

The above makes the model very unfriendly and will avoid to use the same.

PART III  – Removing Protection of the Original File

You must have realized that there are some worksheets which are hidden, may be with database of questions and answers. Unhide the same by right click on the sheet name “DISA QUESTIONS” and press “Unhide”

And the both the worksheets “DISA QUESTIONS” i.e. the existing model and “DISA Question and Answers” are protected. The protection is for good reason that no data is altered by user but as we are in process of redesigning the model we need to unprotect the worksheets. As we dont know the password provided by the Developer of the existing model, we need to break the same by following steps

  1. Open VBA (ALT +F11)
  2. Enter the code at this link in Module 1.
  3. Close the VBA.
  4. Go to View —->Macros—->View Macros—->select “AllInternalPasswords”—->Press Run
  5. Follow the dialogue box that appear and the protection will be removed from the file.

Once the worksheets are unprotected, we can unhide column and rows in  “DISA Question and Answers” by right clicking with “Key Board” and press Unhide.

Download Steps File – Case Study Steps PART III

Download Model Excel File after this step – DISA_Ques bank-3000 ques – Part III

Watch video in action –

PART IV  – Designing A Rough Solution

The ideal solution will address all the Weakness identified at PART I. So lets now once again look at them and try to find out the possible solution.

  • Need to Enter answer  manually in B11.
    • Radio Button to select options like we see in web based exam will be helpful.
  • Need to key a lengthy Key combination to move to “Next” (Ctrl + Shift + N) and “Previous” (Ctrl + Shift + P)
    • A Scroll Bar to move around will be helpful.
  • No Provision to jump say from Question 67 to Question 2087 – you need to press 2020 times “Next” (Ctrl + Shift + N)
    • Question Numbers Range (say 1-100, 101-200….) and facility to select the range from a Combo Box will help in moving around.

All of the above solution(like radio buttons, scroll bars and Combo Box) are part of Developer Tool controls. To know basic of Developer Ribbon and above functions related to Developer Ribbon visit this article.

Download Steps File – Case Study Steps PART IV

Download Model Excel File after this step – DISA_Ques bank-3000 ques – Part IV

Watch video in action –

PART V  – Problems in Above Solution

When we test the Model upto PART IV, we can easily observe two problems:

  • Options REMAINED SELECTED when we go to next question
  • When we change RANGE, the question number don’t start with the FIRST question in that range.

 

Download Steps File – Case Study Steps PART V

Download Model Excel File after this step – DISA_Ques bank-3000 ques – Part V

Watch video in action –

PART VI  – Formatting 

I am not giving a Detail Stpes List and description for formatting part, you can watch the video below and understand easily. However, Conditional formatting when two cells ARE NOT EQUAL can be a new learning if you haven’t come across such situation in past.

Download Steps File – Case Study Steps PART VI

Download Model Excel File after this step – DISA_Ques bank-3000 ques – Part VI

Watch video in action –

PART VII  – Adding Protection

Now we will Protect sheet but after making certain cells (mainly CELL LINKS) to remain “Unlocked” so value can be changed based on Form control positions.

Also We will disable click so that no-one can see the formula or cell content in the Model. This is Important if you haven’t done this earlier. Watch the video and steps file for more details.

Download Steps File – Case Study Steps PART VII

Download Model Excel File after this step – DISA_Ques bank-3000 ques – Part VII

Watch video in action –

 

 

Sharing is Caring, share to your colleagues if you find this helpful.

Cheers,

CA Dhaval Paun

 

Share

Comments

  • Dhaval
    Reply

    Thank you for the feedback.

  • Ankur Riswadkar
    Reply

    Sir,
    Just today,I cleared e-learning ISA course and as you mentioned persuing ISA course from Sept2015 from vadodara and batch was to begin from 2moro but due to inadequate no. of candidates,it has been deferred to Oct2015.
    As you email for 4 days were un attended since I had to under go 18 hourse e learning, today after E learning exam, I got time to go through your day 1 email and to my surprise I got your guidance and download for ISA course. Great relief and thanks for your professional gesture. I definitely feel proud that still such professionals still exists who want to help others by sharing their knowledge of their own will.I shall in your touch in case of any help in the above matter.
    Thank you Sir once again.
    With high Regards,
    CA Ankur Riswadkar
    Regards,

    • Dhaval

      Thank you sir for your kind words. Please feel free to contact me anytime on my email djp.fin.ser@gmail.com

  • Arun Joshi
    Reply

    Sir,

    I’ve already mailed my requirement, but i haven’t receive any reply.

    Kindly confirm the same.

    Thanks

  • G JAGADEESH
    Reply

    THANKYOU SIR FOR SHARING.

Subscribe for Newsletter