• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Populate names of people attending a course on any given date

TerryE123

New Member
HI
Hope someone can help me.
I have a course schedule with names of trainers in column A and dates for a calendar year in row 2
I have validation lists that contain courses assigned to that week and we assign trainers to the courses they are to deliver.
This is a screen shot of a small sample version of that tab
1728497462668.png
on a separate tab, I have a monthly summary where I select name and month and it populates the activity on that day using a filter formula that looks for day and name in the schedule tab.
What I am unable to do is, below each course for each day, is populate the names of the other trainers they will be working with on that day? This screen shot is how I want it to look but i just cannot collate those names. In addition, I want to exclude activites such as Annual Leave, Absent, etc in whatever formula I use for this.
1728499415415.png

Hope someone out there can help with this.
Thanks in advance
 
Last edited:
Please provide a sample workbook - we can't manipulate pictures. Are you using Excel 365 for this?

I want to exclude activites such as Annual Leave, Absent, etc

What is the 'etc.'? We'd need a definitive list of activities to be ignored.
 
I am using Excel 365. Attached is a small sample of what I am using. The definitive list of exclusions is
Absent, Annual Leave, Maternity Leave, Non Working Day

Thanks in advance
 

Attachments

  • SampleQuery.xlsx
    34.7 KB · Views: 8
A starter: In F8 of the Monthly Summary sheet:
Code:
=LET(Colleagues,FILTER(Schedule!$A$6:$A$16,FILTER(Schedule!$B$6:$P$16,Schedule!$B$3:$P$3=F6)=F7,"None"),FILTER(Colleagues,Colleagues<>$C$3))
Copy where you need to.
You'll probably need to add more if_empty arguments to some of the FILTERs, and perhaps add a TAKE to limit the number of colleagues to 4 since you don't have any more space for the formula to spill into.
Additionally, this could be converted to a named lambda formula making it a bit more user friendly, eg.
=OtherAttendees(F7,F6,$C$3)

edit: @AliGW beat me to it…
 
Regarding exclusions (which I missed), set up a table somewhere with those exclusions and name the table Exclusions:

1728562293418.png


then adjust the formula in cell F8 to:
Code:
=LET(Colleagues,FILTER(Schedule!$A$6:$A$16,FILTER(Schedule!$B$6:$P$16,Schedule!$B$3:$P$3=F6)=F7),IF(ISERROR(XMATCH(F7,Exclusions)),FILTER(Colleagues,Colleagues<>$C$3),""))
 

TerryE

How could You write (in other Forum) I was not aware about posting on other forums?

Of course, You've seen from Chandoo.org's front page

New Users - Please Start Here

and You've followed to page
  • Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

AliGW

... but do TerryE know that those should read before posting?
 
Last edited:
How could You write (in other Forum) I was not aware about posting on other forums?

Because he hadn't read the rules on EF, nor had he read them here.

This is solved according to TerryE on EF.
 
I have already apologised. What more do you want?
 

TerryE123

Answers ...
Did You read that my question which You pasted two times?
Why did You skip basic step New Users - Please Start here ?
You wrote that You did something ... in other forum?
I see ...

AliGW
Yes, many members fails to read ... but I would like to know - why?​

... is there any way to change something that everyone will read those?
After reading, everyone would get smoother replies - that no need to ask same details which has written in rules.
Is You opinion that - now, it (skipping) is okay ... everything is fine?
Noted ... yes, noted.
Do it solve something if apologise somewhere else?
It would be better to learn, how do to?
... and to give answers?
TerryE123 asked one question and I answered.
 
@vletm

AliGW​

... but do @TerryE know that those should read before posting?

You have been a moderator here for a long time - so have I on EF. You should know by now that MANY members fail to read the rules before posting, even if you serve them up on a plate. Not reading the T&Cs is something that we are ALL guilty of in life to some degree or other. I don't think you need to hound people over it, especially when they have noted your rebuke and apologised. You are very unforgiving in your approach, I find. Let it go now.
 
Back
Top