I have two columns in my Worksheet. Each column has Data Validation to ensure that the user enters the information in a specific format. The only "variable" that exists is the last three numeric characters. These last three characters may only be used once, in either of the two columns.
I've tried evaluating column values, using MATCH, and coming up with a number. If the number was greater than zero, triggering a Data Validation that shows an error. The only problem is that Data Validation only works on USER ENTRY, not evaluated values.
Does anyone know of a way to keep the Data Validation in place while preventing the last three numbers from being used more than once?
The two column Data Validations are listed below.
I thank you in advance for all of your time and effort!
=AND(LEN(C5)=12,EXACT(LEFT(C5,9),"120R0343-"),ISNUMBER(-MID(C5,ROW(INDIRECT("10:12")),1)))
=AND(LEN(D5)=12,EXACT(LEFT(D5,9),"110R0343-"),ISNUMBER(-MID(D5,ROW(INDIRECT("10:12")),1)))
I've tried evaluating column values, using MATCH, and coming up with a number. If the number was greater than zero, triggering a Data Validation that shows an error. The only problem is that Data Validation only works on USER ENTRY, not evaluated values.
Does anyone know of a way to keep the Data Validation in place while preventing the last three numbers from being used more than once?
The two column Data Validations are listed below.
I thank you in advance for all of your time and effort!
=AND(LEN(C5)=12,EXACT(LEFT(C5,9),"120R0343-"),ISNUMBER(-MID(C5,ROW(INDIRECT("10:12")),1)))
=AND(LEN(D5)=12,EXACT(LEFT(D5,9),"110R0343-"),ISNUMBER(-MID(D5,ROW(INDIRECT("10:12")),1)))