Forum Discussion
What excel formula should I use?
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.
7 Replies
=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.
- Assuming you have the latest Excel FILTER() would be even easier:
=FILTER([LIST OF NAMES], [Col with Y/N]='Y',"")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.
=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.
