Referencing a dynamic cell

Question

I have a worksheet that I enter the answers to quizzes on and then it grades the quiz for each student. I have another worksheet that needs to reference the grade for each student for each quiz. The number of questions varies thus making the issue of locating the grade a little more difficult than if it were, say, 10 questions for each quiz.

If you notice in the screen shot, you will see the bottom section of the previous quiz (25 questions) and the top section of quiz 6, I need to I need to determine the row that "Quiz 6" (two cells) is in so that I can then get the number of questions for Quiz 6, i.e., 20 and then determine how many rows to drop down to get the grade.

On the worksheet that I pulling in just the grade, I determine the number of the quiz, i.e., 6, via column reference. Using that column reference, I need to be able to find its respective quiz, say column 6 = quiz 6, then insert the formula to drop me down to the row where I can pull the quiz grade.

Thanks for your help!

Answer 1

=TRANSPOSE(CHOOSEROWS(EMSTesting_Quiz!E:X,LET(a,MATCH(1,(EMSTesting_Quiz!$U:$U="QUIZ #")*(EMSTesting_Quiz!$W:$W=COLUMN(F1)),0),INDEX(EMSTesting_Quiz!O:O,a)+a+3)))

File sent in private message

Private Messages - Microsoft Community

Answer 2

Here is the file: EMSTesting - Formula Issue

This file contains the following worksheets:

Students: Cell B2 is referenced by four of the other 6 worksheets in the file.

Quiz_Paper: I grade paper quizzes for each student. Has same fixed number of questions (25) for each quiz.

Quiz_Grade: Finds correct quiz in Quiz_Paper, based on column, and pulls in student's quiz grade.

EMSTesting_Quiz: I grade online quizzes for each student. I switched from having 25 questions to 20 questions after quiz #5.

EMS Testing: Finds correct quiz in EMSTesting_Quiz, based on column, and pulls in student's quiz grade. This is where the current formula issue is.

TesetAns: Referenced by COLUMN(B) in both Quiz_Paper and EMSTesting_Quiz.

Chapters: Referenced by ROW(3) in both Quiz_Grade and "EMS Testing"

"EMS Testing" is where the issue is as you will see in column H. Given that I may have quizzes with different number of questions, I need to be able to locate that quiz in EMSTesting_Quiz and pull in that student's grade. In H4, I reference Column(F1) to indicate this is quiz #6.

Answer 3

Could you share a test file? Then I can help you correct it.

For sharing file, you may upload it to OneDrive or any other cloud drive and then post the link here.

*Please make sure you have removed any sensitive or private information in the sample file before uploading.

Answer 4

I inserted this formula with the necessary edits and it gets me the #20 for number of questions. It shows a #SPILL error. When I "evalutate formula" it shows Sequence(20) but the last step of execution goes to the #SPILL error.

Anyway, I did pull up the "20". Once the error is corrected, which I do not know how to do, I need to know the row number that the "20" is on. Here is the formula after I insert the worksheet name I need to check on.

=SEQUENCE(INDEX(EMSTesting_Quiz!O:O,MATCH(1,(EMSTesting_Quiz!U:U="QUIZ #")*(EMSTesting_Quiz!W:W=COLUMN(F1)),0)))

Answer 5

This one? Get 20 based on QUIZ # in column U and the 6 in column W then create 20 rows. What to do next?

=SEQUENCE(INDEX(O:O,MATCH(1,(U:U="QUIZ #")*(W:W=6),0)))