Auto-Updating Quiz Scoreboard with Ranks Per Question

[Synthespian]

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)

Name	Q1	Q2	Q3
Bob	4	3	10
Sue	1	8	5
Tony	5	4	2

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)

Name	Q1	Q2	Q3
Bob	4	7	17
Sue	1	9	14
Tony	5	9	11

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

Name	Q1	Q2	Q3
Bob	2	3	1
Sue	3	1	2
Tony	1	1	3

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.