Lessonwise question generator

This is really confusing... What is your expected result? Column A? Something else? What is the relevance of the stuff in F to J?

Sorry about the random function i just copy pasted the sample data. In each lesson the column B, D and F should be pulled up to the output sheet.

In the output tab, Column F to J: The user needs to type the number of questions for each lesson and according to that input the questions should be generated.

Finally the expected result should show in the Output tab: column A.

I feel as if this would be easier if the questions were on one sheet and in fact one column.
In the attached file I put the questions from lessons 1 and 2 on the Lesson 1 sheet.
The questions are in column D
The lesson and questions type are in column C (type .1 = choose, .2 = T/F, .3 = question)
The RAND() function populates column B
Column A is populated using: =SUMPRODUCT((C$2:C$55=C2)*(B$2:B$55>B2))+1
Column E displays the questions that have been selected using: =IF(A2<=F$1,"S","")
Note that cell F1 needs to be manually populated with the number of questions from each lesson/type that will be used.
The selected questions are displayed in column M using*: =IF(ROWS(A$1:A1)<=G$1,INDEX(D$2:D$55,SMALL(IF(E$2:E$55="S",ROW(D$2:D$55)-ROW(D$1)),ROWS(A$1:A1))),"")
*Denotes an array entered formula which is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Note that G1 is populated using: =F1*COUNTIF(A2:A55,1)
Column L numbers the questions using: =IF(M2<>"",SUM(L1,1),"")
Note that custom formatting 0. is applied to column L
Columns L and M could be moved to a separate sheet.
I believe that all functions used are supported in the 2003 version of Excel, although the .xlsx file format is not, so I imagine you are actually using another version (please update your profile if that is the case).
Let us know if you have any questions.

First, since you are using the 2019 version we can rewrite the formula in column M to read (not an array entered formula):

Formula:

Please Login or Register  to view this content.

Note that cell I1 contains: =SUM(G2:G15)
Assuming that you still want the questions picked randomly (modeled in attached file):
1. Place the Lesson.Types in column F
2. Type the number of questions you want to select from each Lesson.Type in column G
3. Modify the formula in column E to read: =IF(A2<=INDEX(G$2:G$7,MATCH(C2,F$2:F$7,0)),"S","")
If on the other hand you want to pick the questions manually, delete the formula in column E and type an s in column E corresponding to the row of the selected question.
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day and a Merry Christmas.