Forum Discussion
Data Validation on column with concatenated cells
I am trying to use data validation on a column which has a concatenated formula combining 3 cells from other columns to ensure that the combined data is unique (i.e. columns A,B + C can have many duplicated, but the combined data of of Columns A, B +C must be unique). However, the validation doesn't work, I think because the data in the column requiring validation is made using a formula.
Is there any way round this, or other means of ensuring that a combination of cells is unique?
I have attached a file for info. The column I am trying to validate is AW on the Drawing Issue Register worksheet
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.
4 Replies
I know this is an old thread but I have same issue with a drawing register. I have used the conditional formating to alert for any 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.
