Detect Enter Key and search value in a range

[jamiguel77]

Hi friends.

How to do:

the user start capture in cell A1="APPLE" go to A2 and capture "ORANGE" and then the user capture in A3="APPLE" here how to check every capture if exist say a message: "Error, captured on A1"


or only can do the task, with a button and with vba code?


thanks

 

[Fluff]

Select A1:A10 > Data validation > Custom > use this formula
=COUNTIF(A$1:A1,A1)=1

 

[jamiguel77]

Select A1:A10 > Data validation > Custom > use this formula
=COUNTIF(A$1:A1,A1)=1

OOOO

Interesting..
solved problem

thanks

 

[Fluff]

You're welcome & thanks for the feedback

 

[jamiguel77]

friend, tested the sample in a blank sheet, but when i try do same job to a cell: c5 to c1000 not work:

1) Select c5 to c1000 and follow the steps and put this formula:

=COUNTIF(C$1:C1,C1)=1




but not work, wich is wrong?


thanks

 

[Fluff]

You need to change the formula to the first cell like
=COUNTIF(C$5:C5,C5)=1

 

[jamiguel77]

Worked......

other dudes with this topic...

1) i configure a Stop message when user type a duplicate value, but the message not was displayed, only if user type a duplciated value, cant continue, the cursor is in same cell why not display the message but the formula work perfectly?
2) is possible coloring a cell with red, and continue capturing?
3) is possible send a personalzed message: "Error, this data: "+DATA+" was captured on cell: "

 

[Peter_SSs]

Just a further check that you are getting what you want. With the Data Validation suggested, it would not allow you to enter "Apple" in C7 if "Apple" had already been entered in C5 or C6 however it would not stop the user going back to say C5 and entering "Orange" if "Orange" has already been entered anywhere below C5. If that is an issue for you then ..

Select C5:C1000 and use this DV formula.
=COUNTIF(C$5:C$1000,C5)=1

For the idea of stating what value is trying to be entered and where that value already occurs, you would need vba code and also ensure that any user of the sheet was allowing macros to run.

 

[jamiguel77]

Thanks....



i know about VBA i think seriously in type some code for get the JOB

a dude in Custom data validation can i execute my macro? and pass as parameter the cell value?


how to do? or create other topic?

thanks

 

[Peter_SSs]

i know about VBA i think seriously in type some code for get the JOB

a dude in Custom data validation can i execute my macro? and pass as parameter the cell value?


how to do? or create other topic?

Sorry, I'm struggling to understand that - language translation problem? Perhaps you could try again & try to be more more careful with the English if you can.