Forum Discussion
Data Validation on column with concatenated cells
Thanks Hans. I appreciate that is where the problem is. Columns A, B + C individually don't need to (and can't be) unique. It is only the combined information that make up a drawing number (e.g. G(0-)01) that has to be unique (i.e. I can't have to drawing number the same).
as a set of examples:
G(0-)01 -ok
G(0-)02 - ok
G(2-)01 - ok
A(0-)01 - ok
But a further copy of the same data (e.g. another G(0-)01 , not ok)
It looks like VBA may have to be the way to go, but disabling undo would be unacceptable). Otherwise, might just have to live with being careful and checking manually for duplicates.
Data validation only works on cells that are edited directly, not on cells containing a formula.
But columns A and B already have data validation of type List to display a dropdown list. You cannot add data validation to check for uniqueness.
You could add a validation rule to column C of type custom, with formula
=COUNTIF($AW$2:$AW$72,$AW2)=1
This will prevent you from entering a value in column C that would cause a duplicate combination of A. B and C.
But unfortunately, it won't prevent you from changing column A or B to create a duplicate combination.
An alternative would be to write VBA for the Worksheet_Change event, but that might be overkill, and it would disable Undo.
Thanks Hans. I appreciate that is where the problem is. Columns A, B + C individually don't need to (and can't be) unique. It is only the combined information that make up a drawing number (e.g. G(0-)01) that has to be unique (i.e. I can't have to drawing number the same).
as a set of examples:
G(0-)01 -ok
G(0-)02 - ok
G(2-)01 - ok
A(0-)01 - ok
But a further copy of the same data (e.g. another G(0-)01 , not ok)
It looks like VBA may have to be the way to go, but disabling undo would be unacceptable). Otherwise, might just have to live with being careful and checking manually for duplicates.
Creating the data validation rule on column C based on column AW as described in my previous reply will help - I assume that you will mostly enter a value there after entering a value in columns A and B.