What excel formula should I use?

[BMatths]

Hi, 

I've been told to use a specific formula but I do not understand how this formula can be used with what I need to accomplish. 

So I've got a list of people who have or have not attended different events (marked Y if attended and N if not attended). I have the numerous names going down the rows and the different events across the columns at the top.

Now once this has been filled in I need to form automatic registers where for each event, when I put a Y in the cell to say someone has attended, their name automatically goes on the register. 

I was told to use a Vlookup but from what I can understand from playing around with a vlookup I need to search for a name or some value to get the true or false criterion response which isn't the automatic response I'm looking for.

Has anyone got any ideas what formula or formulas I would use? I'm not too skilled with excel so the simplest instructions available would be appreciated. 

Thank you. 

[#2]

Assuming you have the latest Excel FILTER() would be even easier:
=FILTER([LIST OF NAMES], [Col with Y/N]='Y',"")

[#3]

@BMatths 

=FILTER(A2:A27,XLOOKUP(M1,B1:F1,B2:F27)=N1)

 With Office365 or 2021 or Excel online you can apply FILTER function to dynamically select the event and "Y" or "N" as shown in the attached file.

[#4]

@mtarler @OliverScheurich Hi, I can't use a filter as the registers need to be on a separate excel spreadsheet. There are a lot of events and only certain people need to see the data for each club to safeguard data so I need a separate spreadsheet that automatically updates. 

[#5]

@BMatths 

=FILTER(A2:A27,SWITCH(M1,"Event 1",Tabelle31!B2:B27,"Event 2",C2:C27,"Event 3",Tabelle28!U2:U27,"Event 4",Tabelle27!O2:O27,"Event 5",Tabelle26!L2:L27)=N1)

This works in my spreadsheet. 4 of 5 events are located on different sheets ("Tabelle31", "Tabelle28", "Tabelle27" and "Tabelle26") in this example.

[#6]

I believe @BMatths is saying they are in a completely different workbook. The problem then is that worksheet functions will only update from an external workbook when that other workbook is open. You could use power query to get the data and that query can update on open but it still won't be 'live' since you will have to refresh data to get another update.

[#7]

Yes that's what I'm saying. So will it not update if I save the first workbook?

[#8]

my understanding is that if workbook B has references to workbook A and you make changes to workbook A, then:
if the refs in workbook B are cell references they will not update unless book A is open when you have book B open
if the ref is a data connection (e.g. power query) then it can be set to update on open. But if another user updates book A while you have book B open, you will not see that update unless you force a data refresh.
An alternative approach might be to have a single workbook that has the private data on a hidden sheet and had protection turned on. It isn't 100% secure (actually far from that) but for an average user, it works well. I would consider it like those little luggage locks, anyone could pick or break the lock and get in but that would be intentionally doing something they shouldn't. Like they say, locks keep honest people out.
BTW, I went 1 step further when I did this by having a intermediate sheet so I have a login sheet where they put in their name and PW and then a report sheet showing all their information. But that report sheet just pulls data from that intermediate sheet and the intermediate sheet pulls all the data from the actual data. This further helps keep the source of the data abstracted from the casual user.
It all depends on how private that information is. Is it just info they have no business looking at but by no means top secret or is it really confidential information? In my case it is just training records that are by no means top secret, but if they come to see their training records they don't need to sift through everyone else's.