Class Results Based on Teacher

[Darth_Williams]

Good morning,

Is there a way to create a summary sheet where I could enter a teachers name say in cell B2 and a table below will automatically populate with each students name and the grades they have in each topic?

I can make this work for one student but ideally I need to make it bring back every student that teacher is in charge of and I'm not ashamed to admit to being stumped

I have the main data sorted and I know that this is easily achieved with filters, but the powers that be want to see this on a summary page instead of using filters.

Any help will be appreciated.

Thank you

 

[Finalfight40]

Hi Darth

Which version of Excel are you using?

Are you using 365 and have access to the =FILTER function? If so you can use this to get the results on your summary page.

 

[Darth_Williams]

Hi Flight,

I'm on 365 ProPlus. Must admit that I have never heard of the =FILTER function before. I'll have a google and see if that helps.

Cheers

 

[Finalfight40]

Its very new so its normal you might not have heard of it. Take a little look here:

How to use the Excel FILTER function | Exceljet

 

[Darth_Williams]

This is soooo close to what I am looking for, thank you. Do you know how to make it ignore empty cells from the array, so instead of returning a 0 it just returns a blank?

Thanks

 

[Finalfight40]

I'm not sure how to do that with this formula since it is new. So if its possible and someone knows how i would also appreciate if they could share.

But without changing the formula you can highlight the cells -> right click -> format cells -> Custom and change it to 0;-0;;@

This will also get rid of the 0 values however. For example if a student has a grade '0' (if you use that).

Taken from here:

Display or hide zero values

 

[Darth_Williams]

This works to a degree but naturally overwrites the existing format in there. Bloody close though

 

[Finalfight40]

My XL2BB isn't working, ignore this post.

 

[Darth_Williams]

Sorry Flight but I can't see anything.

Basically if I student hasn't taken a particular exam then I would like the result to be a blank cell instead of a zero, otherwise to return the date the exam was taken. The 0;-0;;@ format overwrites the date format and I can't think of a way to have multiple cell formats in place

 

[Finalfight40]

Sorry i was trying to show you an example because it was easier to show but it didn't want to work.

Lets say the student hasn't taken the exam, do you want the row to appear in your filter results?

If you want the exam to appear but with a blank in the result, i'm not sure how to do this other than the method i mentioned above.

However if you don't want the subject row to appear at all, then you can use something like this:

=FILTER(B3:D6, (B3:B6="A")*(C3:C6<>"")*(D3:D6<>""))

Where column B was the teacher, C the student and D was the results. The highlighted part would be the part you would be interested in.