Pulling same data range from multiple sheets depending on selection

Question

I am working on a gradebook for some teachers. I have provided a simplified version HERE

On one of the sheets (the summary one), I am trying to show the grades of the selected student (A3) for the selected subjet (B3). I am pulling the data from three different sheets in the example: Math, Science, English. The pulled data appears in row 6 in the summary tab. My issue is the length of the code.

=IF(B3="Math";IFERROR(INDEX(Math!$B$3:$F$8; MATCH($A$3;Math!$B$3:$B$8;0));"Choisi un élève dans la cellule B4"); IF(B3="Science";IFERROR(INDEX(Science!$B$3:$F$8; MATCH($A$3;Math!$B$3:$B$8;0));"Choisi un élève dans la cellule B4"); IF(B3="English";IFERROR(INDEX(English!$B$3:$F$8; MATCH($A$3;Math!$B$3:$B$8;0));"Choisi un élève dans la cellule B4"))))

I am planning on having 16 subjects and can not figure I more efficient way to get my results. All of the data comes from the same range no matter the tab. The subject is the only variable that would change from selection to selection.

Thanks

Ed Nelson · Accepted Answer · 2016-11-02 13:59:13Z

1

I would do this with query like this:

=query(query(INDIRECT(B3&"!B3:F");"select * ");"select * where Col1 contains '"&A3&"'")

B3& gets your selected sheet name from the drop down and '"&A3&"' gets the student name from the other drop down. This should handle new sheets added students and additional dates.

Using filter seems to solve the problem. Try:

=filter(INDIRECT(H4&"!B7:BR"),INDIRECT(H4&"!B7:B")=B4)

edited Nov 2, 2016 at 13:59

answered Oct 30, 2016 at 15:47

Ed Nelson

10.4k2 gold badges32 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nicholas Chauvin nxchauvin Over a year ago

I tried using the code in the simplified and it worked perfectly. However, when I tried it in the real document it skipped certain cells. Here is the real document : docs.google.com/spreadsheets/d/…. You can see that it worked in B10, but skipped C10 and K10 with the person selected. Any ideas?

Ed Nelson Over a year ago

In a query, in case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values. You have both formulas and text in the same column of data. Without the complete spreadsheet, it is not possible to tell if this is needed. Try making the columns the same data type.

Nicholas Chauvin nxchauvin Over a year ago

Thank you, you are a god among men!

DeeKay789 · Accepted Answer · 2016-10-30 16:07:55Z

If you're happy to add a 'working' tab "All Student Grades" and use column A in your subject sheets then your Summary should stay simple and extensible. In 'Student Summary'!A6 use

=filter('All Student Grades'!C3:G18,'All Student Grades'!B3:B18=$B$3,'All Student Grades'!C3:C18=$A$3)

In the new "All Sudent Grades" in B3 enter:

={Math!A3:F8;Science!A3:F8;English!A3:F8}

Note you only have to enter all the subjects into the array once. The array will automatically fill.

In each of your subjects in cell A2 add the name of the subject In each of your subjects in cell A3 add:

=arrayformula(if(B3:B>0,$A$2,))

The ArrayFormula simply adds the subject to each row of the data automatically as the data grows downwards.

Hope this helps as a starting point.

Collectives™ on Stack Overflow

Pulling same data range from multiple sheets depending on selection

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related