How to autofill data from a row to a column on a different sheet in the same folder?

I've been struggling with some solutions I've found on the forum but after 1.5hrs I'm close to giving up and manually entering data - which is bound to cost me another 28 hrs.
Hoping someone has the solution I'm looking for and is willing to share..

I've exported questionnaire results from Mentimeter to Excel.
The document output is formatted automatically in a way that uses columns for unique respondents, followed by their answers in the same column but along individual cells on that column's row, meaning the first entry is A2 and the last entry is in cell CQ2 or something.

I would like to make this more user-friendly by:
1) putting each respondent's answers in their own sheet in the folder, and
2) by listing the questions in the first column and the answers in the next columns pretty much 'the other way around'.

Currently it looks like this; the answers I need are listed in !VotersF3 to !VotersCQ3.
The next respondent's answers are in !VotersF4 through to !VotersCQ4 and so on.

What I'm looking for would ideally display answers in !AnswerA3 through to A80.
When I manually select !AnswerA3 and click on !VotersF3, logically it does what I want.
When I then drag down to autofill, equally logically the sheet enters !VotersF4 instead of !VotersB3 as it's a row vs column problem.

I've tried different version of INDEX and TRANSPOSE but I can't get a working formula from that.
Would anyone be able to provide me with the correct solution for doing this?
I've got another 20+ respondents answers that need to be 'easy to view' instead of scrolling 500 screens horizontally....

Thank you Excel wizards! :)