Dynamic drop list which removes options that are already chosen

Hello

Im trying to make a time Schedule for workers
each hour has its own row and each column is represents a room

i would like to make it so that in each hour there cannot be the same name chosen twice

i have sucecesfuly made it on a single row by using another table (see MATH sheet) But i feel like that solution is not very elegant and i will have hard times copying it to every single hour, since i would have to copy the whole table every time.

Is there a better solution that i could easier copy to each hour?

EXTRA:
would it be possible to add another list for choosing the type of work (next to the names) where you could define what you could and couldnt do in which room and it would only offer you those options?

or add another condition that would remove certain options in certain rooms

Thanks for help

Tried it out, it doesnt do anything, what is it supposed to do?
i tried checking the code for , and ;

Hi,

Put the mrice code - without having to change anything in it - in the spreadsheet module, and then choose from one DV and you will see that you've chosen in a DV does not appear in another.

i tried checking the code for , and ;

You can use that ONLY in formulas NOT in VBA code.

I believe with "choose one DV" you mean the yellow cells, if its so, then it doesnt work, i could have most of the name twice there.
It also doesnt seem to do anything else, i cant figure it out, not even in the file mrice has posted

the code seems to work with color and im not sure why, its not doing what i needed it to do, but maybe im not meeting some conditions?


anyway im interested in this solution but i found a workaround meanwhile that checks for the same word and hits me with an error message if there are meanwhile, a dissapearing droplist would be better though

it will look like if you took every thick bordered cell and copied it to the whole Schedule - the first row will be everywhere so to say

what you desribed is what i want it to do, but it doesnt work, i could put almost any name twice there before it dissapeared

it seems to only delete the name from the list of the first right cell
example
i entered NAME1 in the room1 cell, the room2 cell doesnt offer name1 but all of the others do, after that i wrote name 1 in the room4 cell, room5 doesnt offer it and the rest does still, it only removes it from the second one, i would need it to remove options from the entire row

Oh i see, well no, only the yellow parts will be filled - not every cell but every third one

Tested it out, it seems to work perfectly in the yellow part, if there is no background color it doesnt work but if there is one or even two different it seems to work, though once i managed to get one name twice and that kinda worries me.

My problem is now, could you somehow do it without being based on colors? My colors keep changing all the time, they change together with the names and it crashes when i choose a name and it tries to change the color XD

i wouldnt mind giving it a certain range as long as i can give it each cell and itll only count rows (you saw what it looks like, i only need rows to delete each other and not every cell)
Sorry for my noobiness and bad explaining

Thanks for the fast reply

This doesnt seem to work at all though, ill attach what i tested it with, and the other attachement is what i plan on using it with later if we get it working, just so you see what i have to change later or what the problems are

Awesome! seems to work the way it should, youre genius!

It even works with new cells DV i added after

Now tell me, and you had that question coming, what do i need to adjust when using this formula for another file? from what i can tell the "seznamy" name will probably have to be changed.. and there, is that row count? like every X row? a colorful guide would be awesome, thanks!

Doesnt let me to give you anymore reputation, sorry

wait i was happy too early, changing the names or deleting them or anything results in a complete mess!

no idea why - the list is linked to them, even though i deleted names 7 - 12, instead of offering only names 1-7 it offers me 7-12 which are deleted o.O
another cell only offers me name 2 while all around are empty
and other funny random stuff

one row works fine, but doesnt delete the names, but at least it works

once i did somethign and it errored and offered "debug" so i clicked it and this row got yellow

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=NameString

Yes that helped me understand it a lot! But i dont see where the bugs come from when i change the names, it also looks quite difficult to adapt, i dont think sheet name would be enough, i would also have to change the cells adresses in the sheet and the counting as well... im scared to get lost in it, and scared that the bugs will still be there

Your solution deletes names from the entire document and not each row good try though, if one has just one place to use this, it is definately a better way than the row count i found first

Thanks, i appreciate your time and it works, cant use it afterall because id need it in the same sheet and it takes too much space, but its great to know it can be done properly

wtf, where did you hide all the stuff?! XD

ohhhh sneaky