Exam Question bank generator for different students categories

[xkotx]

Hi Guys,
Is there any chance that someone could help me with creating an excel question bank generator for 5 different student category ? Some questions can be applicable to two or three different student category.

For now my "data base" example looks like that:

The ideal solution would be to pick student category, for example A + C or just single A or B and that excel based on applicability(Y- yes / N-No) will produce list of questions that are applicable to that Student category and then list those questions in separate sheet? I'm not sure is something like that even possible in excel...
All help will be very appreciated.

Thank you!

 

[xkotx]

Hi Peter,
Version updated, thank you.

 

[Peter_SSs]

Hi Peter,
Version updated, thank you.

Thanks for doing that.

Now knowing that you have 365, if you are interested, your task could very easily be achieved by formulas. Here is one way. You may need to adjust the ranges in the formulas to suit your data ranges.

• On Sheet1, mark your categories of interest as I have an example in row 3 (could be elsewhere if required) with "1"s. I have chosen categories B & D.
• Enter the simple helper column formula in column I and copy down as far as your data goes (or further if your data might expand). This column can then be hidden if you want.
• On Sheet2, place the single formula in E1 (no need to copy across) and the single formula in A2 (no need to copy anywhere)

Done. Now if you change where the "1"s are on the first sheet, these Sheet2 results will automatically update without the need to run a macro.

xkotx.xlsm
	A	B	C	D	E	F	G	H	I
1
2		Question	A	B	C	D	F
3				1		1
4	Chapter 2.5	Question 1	Y	Y	Y	Y	Y		YY
5	Chapter 2.8	Question 2	N	N	N	Y	N		NY
6	Chapter 2.98	Question 3	N	N	N	Y	N		NY
7	Chapter 3.5	Question 4	N	Y	N	Y	N		YY
8									00
9									00
Sheet1

Cell Formulas
Range	Formula
I4:I9	I4	=CONCAT(IF(C$3:G$3=1,C4:G4,""))

xkotx.xlsm
	A	B	C	D	E	F	G	H	I
1	Questions			Categories:	B	D
2	Chapter 2.5	Question 1
3	Chapter 3.5	Question 4
4
5
Sheet2

Cell Formulas
Range	Formula
E1:F1	E1	=FILTER(Sheet1!C2:G2,Sheet1!C3:G3=1)
A2:B3	A2	=FILTER(Sheet1!A4:B100,Sheet1!I4:I100=REPT("Y",COUNTA(E1#)),"")
Dynamic array formulas.