If you want to limit the choices available for the values that can be entered into a cell, you could use a data validation list.
In the example image below, we are creating a roster for the teachers that are on playground duty during the week.
The data is laid out as a table, and I am using conditional formatting to highlight any duplicates across rows, and down columns. No teacher can be on duty twice in the same day, or be on duty for the same period during the week.
So when I select Claire for A.M. Recess & Lunch on Thursday, my conditional formatting rules warn me. Likewise when I assign Bob to A.M. Recess & Lunch on Friday, and Lunch on Wednesday, the CF rules let me know.
This works nicely but we have to set up data validation for each cell in the table. If we write a little VBA, we can use just one DV list to populate the entire row.
In the image below we can see I am using just one list at the start of each row, and each time I make a selection, the next empty cell in the row is populated.
Not only that, if I make a mistake, I can choose the blank first entry in the list, and this deletes the last value in the row. Or I can press the Delete key to do the same thing.
I can also delete cells in the middle of the row (and they don't have to be adjacent cells), then use the list to fill in the gaps.
This table is using the same conditional formatting rules as the first table.
The VBA uses the Worksheet_Change event so you'll find the code in the Sheet1 module.
Enter your email address below to download the sample workbook.
Right click this link and download the .xlsm workbook
I’ve had to deal with similar (multiple condition / exclusivity) situations as this before, and usually settle for a custom formula to apply the validation rules, but in doing so give up the drop-down list functionality.
In your example, the following custom formula in the Settings Source field for cell C7 will apply the rules the school principal specified:
=AND( COUNTIFS($C7:$G7, C7)<=1, COUNTIFS( C$7:C$9, C7) <=1 )
I may now look to use some VBA to get the best of both worlds.
Thanks for this, always good to have other approaches to a problem.
This was good and very use full, I am still learning to creath forms in VBA