Excel formula to display values from one sheet to another based on various conditions

[kinkate18nic]

I have an excel master sheet in the below format:

There are 29 subjects, however a student might opt for max 5 or 6 subjects out of this 29 and only those subjects will have marks data, the marks will be blank for the subjects which is not opted by the students. Only one school name is shown in this sheet, however there will be multiple school name(cno is the school code).

I want the above master data in school wise sheets of the below format:

Here, what I will do is just paste the roll number of students and rest of the data should automatically populate from the master sheet. Here I have max 6 subjects as placeholders, when i place the rollno say the first one, the formula should check which subjects are enrolled by student and display the subject names in the columns and marks data below each of the.


Is this possible using combinations of xlookup or any suggestion wold be appreciated.

Thanks

 

[Anthony47]

The formula uses the function LET, that let you declare a Variable and its value (this is my best not technical description of that feature!), in pair. These are the Variable /their definition that you need to customize the formula:

       myRoll / D6                            'myRoll points to Roll No on the output sheet
myBT / 'Class-9-IA-PR-DCF '!$A$6:$CB$15      'this is the Big Table on the source sheet, modify the last Row (15, in my test)
myCNO / A6                                   'myCNO points to the CNO on the Output Sheet; I USED COLUMN A

I forgot to mention other important parametres:

head,'Class-9-IA-PR-DCF '!$A$3:$CB$3                                       'head identify the "headers" 
(INDEX(myBT,0,10))                                                         'here 10 means column J, the column with the Roll No in the "big table"
FILTER(myChoices,myChoices<>"")-11                                         'here 11 means column K, the first SCODE
ISERROR(MATCH(5*INT((COLUMN(myRow)-11)/5)                                  '11=col K as above; 5 is the number of columns for each SUBJ

So you have to modify myBT (my Big Table) and head (headers) to start from A and end at EU. Adapt for Roll No not in column J. Adapt for SCODE not starting from K. Adapt for number of columns for each SUBJ <> 5

Then cross the fingers

 

[kinkate18nic]

I forgot to mention other important parametres:

head,'Class-9-IA-PR-DCF '!$A$3:$CB$3                                       'head identify the "headers"
(INDEX(myBT,0,10))                                                         'here 10 means column J, the column with the Roll No in the "big table"
FILTER(myChoices,myChoices<>"")-11                                         'here 11 means column K, the first SCODE
ISERROR(MATCH(5*INT((COLUMN(myRow)-11)/5)                                  '11=col K as above; 5 is the number of columns for each SUBJ

So you have to modify myBT (my Big Table) and head (headers) to start from A and end at EU. Adapt for Roll No not in column J. Adapt for SCODE not starting from K. Adapt for number of columns for each SUBJ <> 5

Then cross the fingers

I made the necessary changes and its working for the additional subjects and additional columns. Thanks for the guidance.