False duplicates - Excel 2007

[dasadler]

I have the worksheet and I was entering data when, all of a sudden a msg pops up saying duplicate (see below). I am not using data validation as there will be duplicates and there is no VBA module checking for duplicates. I close and reopen Excel and have the same issue. It seems to be only one cell as I can enter data into surrounding cells without problem.

Any idea what causes this and how to stop it?

[HansV]

Could you attach a copy of the worksheet? You can remove or clear everything else from the copy.

[dasadler]

Sure - here it is the bad cell is H371

[HansV]

It's data validation alright - it applies to H365:H397. Did you copy other cells into this range?

S0856.png

Anyway, if you select H365:H397, click Data Validation on the Data tab of the ribbon, then Clear All, you should be able to enter data normally again.

[dasadler]

Thanks a lot. I have certainly copied and pasted from another worksheet where I did have such data validation. I did not know that the data validation comes with the cell when copied/pasted. In any case, I thought I always did a paste value... that would not copy data validation, would it?

[dasadler]

Something else... the Duplicate msg would pop up regardless of what I entered in the cell so there were actually no duplicates. What did that msg appear?

[Rudi]

I thought I always did a paste value... that would not copy data validation, would it?

You are correct, if you paste values it will not paste the validation. You were probably distracted when you pasted at that moment.

...the Duplicate msg would pop up regardless of what I entered in the cell so there were actually no duplicates. What did that msg appear?

The SUMPRODUCT formula you have in the Validation dialog was probably checking for duplicates across multiple columns; Col B, Col J, etc... If you just typed content down the Col B, that would not constitute a duplicate at that point since it evaluates a duplicate to be TRUE only if all column values (B, J, etc) in that row match all column values (B, J, etc) in another row.