08-14-2020 03:33 AM
08-14-2020 03:33 AM
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
08-14-2020 03:57 AM
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
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.
08-14-2020 04:20 AMSolution
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-)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.
08-14-2020 04:34 AM
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.