Basic Query Autocomplete and Duplication

[inwell]

Hi,
1)I read that excel uses autocomplete/autofill to fill data in same column but As soon as an empty cell is left in a column, AutoComplete is interrupted..

is it true...? (my excel sheet is auto-completing data after cell breaks)

2)I used formula to stop duplication in excel column (field- name)..it works well while i type the duplicate entry,but wont work when copy -paste is used..it allows duplicate entry.

(ref..image attached)excel2.JPG

[:) Sixthsense :)]

Both of your statements are true

Auto complete will not work when it encounters blank cells
Data Validation only work for manual input entries and copy paste will override the applied validation setting.

[inwell]

thank you sir..

if first statement is true..? why my sheet is autofilling the record after break..?(image for ref)

second... what can i do if i want to stop duplication using copy-paste too ?(i am school student and dont know programming )

regards

[:) Sixthsense :)]

May be the cells which showing as blank in the picture will be having spaces in those cells so that the autocomplete is not getting any break because of non blank cells.

Regarding your second query just do right click on the sheet tab and select view code and paste the below code. Return to excel and check... Now it will not allow you to do copy paste action on D column.

Please Login or Register  to view this content.

[inwell]

sir,
i tried that.. but the problem is it stopped all the copy paste work for that column... though it is not duplicate..(totally new entry from other column/sheet)

second....what is diff between these two..

[=COUNTIF($D$1:$D$30,D10)=1] and [=COUNTIF($D$1:$D$30,D10)<=1]