data validation problem

[samuel kelly]

I have made a validation in the bottom table so that if someones initials comes up twice in the same column a message will come up saying i am trying to double book this person, Which works well.

However when i want to book someone in for a week long i drag there cell with initials along the week, when i drag cells across the data validation does not work, it doesnt pick up if there initals have already been put in other cells in some of the same columns.

How do i fix this please??

plpl.png

[6StringJazzer]

I have made a validation in the bottom table so that if someones initials comes up twice in the same column a message will come up saying i am trying to double book this person

Please give us the details on how you did this.

Better yet attach your actual file. Screen shots are seldom useful.

[samuel kelly]

I have attached document now,

thanks.

[6StringJazzer]

This is a design "feature" of Excel. If you fill or paste a cell that has data validation, even though that data validation will be carried along to the destination for future changes, the data validation rule will not be checked upon the fill/paste.

Here are a few ways to deal with this, that I know of:

1. Use conditional formatting to check for duplicate entries and show some sort of flag, such as a fill color, when duplicates occur (easy)
2. Use additional formulas to check for duplicate entries and show some sort of flag, such as text at the bottom of a column when duplicates occur (harder)
3. Use VBA to check for duplicates when data changes (easy coding, but not if you don't know VBA)