dynamic CONDITIONAL dependent list

Hello!

Id like to make a dependent list where you would choose an ability or (service) and then could choose the right person according to it

that is easy to make but not with the following conditions:

the names of the abilities and people can be changed at any time

abilities and people can be added/removed

people can learn new abilities thus i have to be able to change what they can do or not and thus change the list again thats the reason

i would like to only use one table



here is what i came up with:
i made a table with YES and NO options for all people and abilities.
Could i use some kind of IF function that defines to only let names on the list that have a YES in the right place?

Its hard to explain so take a look yourself

EXAMPLE
i choose ability3 in the first drop list, the second drop list should only offer me the names of the people that have YES assigned there

----------------------------------------

It should be dynamic so that if i change an ability, name, or yes or no, the drop list should adjust

the choices are in the MATH sheet, the results can be on a different sheet

Thanks for the help

I feel like making a table will be a part of the solution, however this requiers to add each name and ability combination by hand, id rather just click yes and no to create the able...
perhaps some function could do that but im not sure, all it would need is "if the crossing of ability1 and name1 = yes (in the table of YES and NOs) write name1 in the row of ability1 (to the other table)

of course all those names would have to actually be cell adreses to keep them dynamic... anyone knows how to formulate that? i only know basics and i have no idea

this would result in three tables, one being the input, one the formulas and one the table output, i guess. or maybe tables can work with formulas so the output would be the formulas itself?

I have succesfuly made a "table" replacing the all YES with the names of the people, thus now we can use THAT table to create the choices necesary (TABLES OF PEOPLE 2)


i couldnt make it a proper table because the headers of that table would be a text ad i need them to stay linked to the sheet PEOPLE2

Is it NOW possible to make it a dynamic list like before and to exclude all the blanks?

I have no idea how you made it but it seems to work perfectly fine! also i dont see any difference between the last two files?

are you only using the grey selected part? could i delete the other sheets?

Now i see it! You basically did what i had in mind but in a more fancy way! haha, i think i can use that!

SIDE NOTE
what does errorif do

how did you solve the blanks? in another project i have a list where the blanks really ruin the droplist, but you seem to have somehow extracted them

very nice, thanks for the explanation! woud be a good function to hide mistakes from homeworks XD