How can I write a formula to reference multiple dependencies and fill in specific results?

dyoung

I'm looking to write a function to improve a very manual event staffing procedure. I have a sheet that has staff member names in rows and columns showing various skills that are trained in the company. If there is an "x" in a cell under a skill it means the staff member in that row is trained on that skill. We have a separate sheet showing upcoming events, with a row which is populated with indicators of which skills are needed to operate the event (those indicators match the headers in the staff skills sheet). Note that these are in a single cell separated by commas (ex: "Lead, Backup, Playback, Record"). Is it possible to write a formula in an adjacent sell on the event list sheet that will show the names of qualified staff members for each call based on the listed required skills? If not something that's easy for me to create on my own (with basic programming/excel function experience), can you recommend any contractors for this project?

Jennifer Kurtz

I think you could probably do this without too much difficulty! The JOIN(COLLECT functions will allow you to pull together multiple names (staff) who meet criteria you specify.

How many specific "skills" are you tracking? (And if you can share any screenshots of your columns, without any sensitive data, of course, that's always helpful!)

dyoung

Thank you @Jennifer Kurtz for the response! Below are some examples of what the skill headers could be. It would be possible for us to match the headers to the requirement tags, but Ideally, the event sheet requirements cell could have a tag that has multiple dependencies. So if the tag was "premium" for the event, it would only show the names of staff members that have an "x" in a set of specific columns.

Jennifer Kurtz

@dyoung - thanks for the info! One idea that may help to simplify your formula would be to use a multi-select drop down of all the skills (on the staff sheet) and requirements (on the Events) sheet. You could then use the "HAS" function to evaluate which staff have all the necessary skills to meet the requirements.

However, this would mean either adding to or replacing the individual skill columns on your staff sheet with a multi-select drop-down, and doing the same on your Event requirements sheet. …My hunch is that going this route will be simpler than the alternative of maybe several helper columns and very long strings of IF & AND statements… (Honestly, I'm not quite sure where I'd start with that sort of thing - though if you wanted to try there are many smarter formula gurus around here that could chime in! :D )

dyoung

Very helpful! Thanks so much @Jennifer Kurtz