Alpha Numeric Code - Data Validation

[cameronb]

I am looking to ensure that data is only entered into a column that follows the format "AB####" so the first code would be AB0001 second AB0002.

Is it possible to set data validation rules that would only allow the first two letters AB and then four numbers (ideally the next number from the preceding row)?

 

[Jon von der Heyden]

You could use a custom validation in Data Validation:

=AND(EXACT(LEFT(A1,2),"AB"),ISNUMBER(--(MID(A1,3,255)))

But it sound like you need a formula rather to generate the next key.

Example, if A1 has the key, the next key in A2:

="AB"&MID(A1,3,255)+1

 

[cameronb]

I suppose I am looking for a data validation rule that will not allow anything other than the next logical code in the cell

i.e. if the preceeding cell had AB0001 then the cell will only allow AB0002

 

[Jon von der Heyden]

But the point I was making was that you could make it easier for a user by having it auto-generate the key, rather than expect them to manually enter it. That was the point of the 2nd formula that I provided.

 

[jasonb75]

You can't validate increments in alpha-numeric codes directly with DV, but you can with a helper cell.

Somewhere safe, maybe a in hidden row at the top of your sheet, enter the formula

="AB"&TEXT(COUNTA(C2:C25)+1,"0000")

C2:C25 was my test range, this should refer to the range that the validation should apply to.

Apply the data validation rule to the same range, using allow = list, source will be the cell with the formula above, must be set absolute, i.e. =$A$1

If you allow in cell dropdown you get the next in the series generated by the DV