Hi there,
I'm making a sheet to analyze livestream participation and want to make the document automatically list the students who are absent for a given event (chosen by drop-down menu).
In Sheet A (see image), I have entered "Real name" of two participants. And underneath three livestream events marked them as Absent.
In Sheet B (see image), I want the names of any absent participant for a given livestream event to appear (can be selected using the dropdown in the top left corner).
So, using the examples in the images, if I choose Livestream event "Kick-Off", "Alibaba Story" or "PEST", I want both Andreas and Rasmus to appear as a list. And if I delete "Absent" in either of the events, the name should not appear on the list in Sheet B.
And, if, lets say, the list got 100 names on it, I want the names of those who are absent for any of those events to appear when I choose either event.
I'd then make a formula to say if "x real name" then (DingTalk Name, Group Name, Tutor) to fill in the rest based on the name that appears in the list.
I have tried with Index + Match and Lookup, but I'm not really able to figure out the logic that says: "When X event is chosen -> lookup "Absent" in Y Column -> return name in 3rd column."
Hope someone got some good ideas. I'm new to this forum, so not sure if I can upload the file if that is of any help.
Looking forward to hearing from you - thank you very much in advance!
Sincerely,
Andreas
I'm making a sheet to analyze livestream participation and want to make the document automatically list the students who are absent for a given event (chosen by drop-down menu).
In Sheet A (see image), I have entered "Real name" of two participants. And underneath three livestream events marked them as Absent.
In Sheet B (see image), I want the names of any absent participant for a given livestream event to appear (can be selected using the dropdown in the top left corner).
So, using the examples in the images, if I choose Livestream event "Kick-Off", "Alibaba Story" or "PEST", I want both Andreas and Rasmus to appear as a list. And if I delete "Absent" in either of the events, the name should not appear on the list in Sheet B.
And, if, lets say, the list got 100 names on it, I want the names of those who are absent for any of those events to appear when I choose either event.
I'd then make a formula to say if "x real name" then (DingTalk Name, Group Name, Tutor) to fill in the rest based on the name that appears in the list.
I have tried with Index + Match and Lookup, but I'm not really able to figure out the logic that says: "When X event is chosen -> lookup "Absent" in Y Column -> return name in 3rd column."
Hope someone got some good ideas. I'm new to this forum, so not sure if I can upload the file if that is of any help.
Looking forward to hearing from you - thank you very much in advance!
Sincerely,
Andreas