Sheet to search for name on other tabs

[Rafaelbf1985]

Hi guys, I've been trying to create an attendance sheet for the school I work for, but I didn't manage to achieve what I need using the little I've seen around or found on the internet. I hope someone can advise me please. 

The sheet I am trying to do, has a tab with "all students", and its contact details. 

The next few tabs are of students that meets certain criteria like, "didn't book" a revision class, "booked 1st class", "attended 1st class", and "didn't attend the 1st" class. 

I would like to be able to bring in all the date to the first tab, and the "search" to  be based on their name, as the software would be constant on this (bear in mind it does have a space between name and last name, as I noticed it was an issue with what I tried)   

So lets say, students that didn't book a class at all, the cell corresponding to that student on tab A column H, would turn red, or place a Yes or a No, some sort of marking which I can then sort by that. 

Bear in mind, I will have to update the attendance tabs monthly, if that makes any difference. I uploaded a sample sheet.

Any help is very appreciate and I promise to pass the kindness forward. 

Rafael 

[#2]

@Rafaelbf1985 

That could be like

=IF(ISNA(MATCH($B4,Table_1[[#All],[Column2]],0)),"-","x")

but with repeated names that won't work correctly (e.g. you have two Andrea)

[#3]

You are a gent @Sergei Baklan ! 

That is exactly what I need. 

On your version we will have last names too. That was just a dummy sheet. 

Thank you very much, you spared another soul hours of purgatory! 

Have a great weekend! 

Rafael 

[#4]

@Rafaelbf1985 , you are welcome, glad to help

[#5]

@Sergei Baklan 

If I could abuse a bit more of you kindness and knowledge, could I ask you for one further question?

On the formula you helped me with, it works perfectly, although one of the other teachers had the request of the formula displaying the data found instead of an "x", for one of the many parameters we monitor. 

What I am trying to say is, one of things we monitor, is the attendance, which would always be a number between zero and "something".

Is there a way to adapt this formula to instead of diplaying "-" or "x" it would display the found number for that name? 

The data is on column "M" on sheet "PRESENCE_COUNT" 

Again thank you very much for your help! 

=IF(ISNA(MATCH($A116,PRESENCE_COUNT!$M$4:$M$1002,0)),"-","x")

[#6]

@Rafaelbf1985 

Sorry, but I found no PRESENCE_COUNT sheet in the file

[#7]

@Sergei Baklan 

Apologies, I forgot to upload an updated version. 

So the number found on the column M "CountPresence" of the tab PRESENCE_COUNT, to appear on the All_Students tab column H. 

As before, it would need to search for the entry based on the name (each student would have name and last name to avoid conflicts) 

And Ideally, it display the found number please. 

Once more, very appreciated for helping with this. 

Thank you 

Rafael 

[#8]

@Rafaelbf1985 

It's better to use Tables, with ranges formula could be

=IF(ISNA(
      MATCH($B4,
                   Didnt_Book_at_all!$B$2:INDEX(Didnt_Book_at_all!$B:$B,COUNTA(Didnt_Book_at_all!$B:$B)),
                   0)),
      "-",
      INDEX(PRESENCE_COUNT!$M$4:INDEX(PRESENCE_COUNT!$M:$M, COUNTA(PRESENCE_COUNT!$A:$A)+2),
                  MATCH(B4,
                       PRESENCE_COUNT!$A$4:INDEX(PRESENCE_COUNT!$A:$A, COUNTA(PRESENCE_COUNT!$A:$A)+2),
                        0 )
))

[#9]

@Sergei Baklan 

It works like a charm! 

You have a real superpower Sergei. The size and complexity of that equation is perplexing! 

Thank you very much! Very appreciated! 

Rafael

[#10]

@Rafaelbf1985 , glad to help.

Again, if use Tables formulas will be shorter, more reliable and easier in maintenance.