Auto-Updating Quiz Scoreboard with Ranks Per Question

Synthespian

New Member
Joined
Jan 27, 2015
Messages
1
I'm running a quiz over zoom (who isn't these days :)), and I've been trying to build a scoreboard in excel behind the scenes.
Depending on the answer per question, you can score different points with each question.

What I would like to have is a function that gives me the score each person's got per question, their cumulative score and therefore their rank after each question (or even each set of 5 questions as well).
Currently I've got this split over a number of different sheets:

Player sheet, with a playerID so I can vlookup/match against for the following:

Score Per Question (worked out by a formula from another sheet)
NameQ1Q2Q3
Bob4310
Sue185
Tony542

I then have a second sheet
Cumulative Score (which does very generic =IFERROR(SUM(Scoring!$E3:F3),"0") to extend and increase per question column)
NameQ1Q2Q3
Bob4717
Sue1914
Tony5911

I then have a third sheet to rank per column of cumulative score:
=RANK(CumulScore!D2,CumulScore!D$2:D501)

NameQ1Q2Q3
Bob231
Sue312
Tony113

I've got another sheet that sums Q1-5 and Q6-10, etc, and then alongside it uses rank to rank after every 5/10/15 questions (just in case)

Basically I need a way to export that quickly per question.
(nice to have, directly into a powerpoint slide, but I can copy paste from excel into it as well)

Currently I have a score sheet that has a list of the players, and I have to update the "col_index_num" to the one I want to get the correct rank & score. Then I need to sort it by rank.
I need a faster way to get to that final sorted rank list, per question.

Options I've thought of:
1) A pre-set tab per question (but how could I sort that automatically)
2) Using a (question) number (e.g. 1,2 ... 11) in another specific field on a master sheet (the question that I'm scoring) that I update manually the once or use a macro to move the question number on by 1 (*see below for pivot impact*) to replace the col_index_num being a fixed value. Basically use a field to declare the column I want to use the in lookup (and given other columns I need per sheet probably with some maths to be not the exact value I enter as the question number). I've searched and searched and found no way to do that).
3) Database + programming (beyond me :P)

Nice to Haves:
* I have a pivot chart and a pivot table per question that using a macro would autoupdate from one column (question) to the next, when I want to update the round I'm reporting.
Again, I can make a sheet per question.

Thanks in advance.
I've got a basic working set of sheets / powerpoint but if I want to increase the number of people entering, I need to increase the automation.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Watch MrExcel Video

Forum statistics

Threads
1,126,953
Messages
5,621,799
Members
415,857
Latest member
braunReivn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top