Validate if cell already has a value in it

**mickwooduclan** · 01-07-2013, 10:04 AM

I need some vba which checks if a user has already entered data into a cell. Basically, imagine 4 non-adjacent cells (eg. A5, A12, A16, A20). If the user adds data to A5 and then tries to add data to A12 (or A16 or A20) I need to stop this. The user can only add data to one of the four cells. Thanks for any help.

**benishiryo** · 01-07-2013, 10:28 AM

hi mickwooduclan, welcome to the forum. here'a non-VBA way of doing it. press the CTRL key & click on the 4 cells. not sure if Excel 2002 has the same shortcut. press ALT + D + L to go to the Data Validation Window. put a custom formula like this:
=COUNTA($A$5,$A$12,$A$16,$A$20)<=1

you can also put an error message like i have. please see attached. i remember answering a similar question & a user Jakobshavn had a VBA solution to it. unable to find the thread though.

**barryleajo** · 01-07-2013, 10:31 AM

Hello Mick
With acknowledgement to benishiro here is a bare-bones VBA option to consider

Please Login or Register  to view this content.

barry

**mike7952** · 01-07-2013, 10:36 AM

Adding to barry's solution. You'll need to select another cell otherwise user will still be able to enter data into the cell. See code highlighted in red.

Please Login or Register  to view this content.

**mikerickson** · 01-07-2013, 10:38 AM

You could use the custom validation formula =(COUNTA($A$5, $A$12, $A$16, $A$20)<=1) in all four of the cells.

**mickwooduclan** · 01-07-2013, 10:50 AM

Many thanks everyone - lots of solutions - I like the custom validation formula but I'll check them all.

**mickwooduclan** · 01-08-2013, 10:24 AM

I've just realised that I need to have two validations in the same cell. The first validation checks whether the user has input between (eg) 25.4 and 30 (I'm using allow decimals between two values). The second validation is the original subject of this thread - eg. =(COUNTA($A$5, $A$12, $A$16, $A$20)<=1). So, can I concatenate two lots of data validation somehow?

**benishiryo** · 01-08-2013, 10:32 AM

change it to this:
=AND(COUNTA($A$5,$A$12,$A$16,$A$20)<=1,SUM($A$5,$A$12,$A$16,$A$20)>=25.4,SUM($A$5,$A$12,$A$16,$A$20)<=30)

**mickwooduclan** · 01-08-2013, 10:38 AM

Not quite - each of the 4 cells will validate against different numbers - In A5 between 25.4 and 30, in A12 between 18.4 and 25.3 and so on. If someone score 27.2 (in A5) they can't also score 23.7 in A12. Hope that makes sense. Thank you again for your help.