Auto Generate

[Pestomania]

This may be a dumb questions, but any help is appreciated.

I am working with a table that has 3 columns, below is an example:

I am trying to find a way that when "Department" is filled in, the unique code will auto fill with the department abbreviations (vlookup from "Department Abbreviations" tab) and chooses the next unique identifier in the sequence. Everything I do makes it change the numbers when I sort the table which should not happen.

I know Microsoft Access creates the databases with auto numbers that do not change no matter how you sort it. Is there a way to create this in Excel?

Department	Unique Code	Description
Facilities	FACT-1001	Test 1
Facilities	FACT-1002	Test 2
Production	PROD-1001	Test 3
Facilities	FACT-1003	Test 4

 

[Pestomania]

That is awesome!! I only fixed one thing that it errored on:

End if instead of End With and removed an End If.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
    If .Row = 1 Or .Column > 1 Or .Count > 1 Then Exit Sub
        If .Value <> "" Then
            .Offset(0, 1).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],Table_Department,2,0)&TEXT(COUNTIF(C1:C1,RC[-1]),""-1000""),""No Match"")"
            .Offset(0, 1).Value = .Offset(0, 1).Value
        Else
            .Offset(0, 1).ClearContents
        End If
  
End With
End Sub

 

[Pestomania]

Is there anyway to turn off the calculated column for only this specific column?

 

[jasonb75]

End if instead of End With and removed an End If.

Sorry, I did the edit on my phone, couldn't test it.

Is there anyway to turn off the calculated column for only this specific column?

Not sure what you mean, there is nothing to calculate in the column. If you mean the existing formula(s) then just copy and pastespecial > values. The code overwrites the formula before it ends to prevent calc changes later.

 

[Pestomania]

Sorry. It is going into a table in the worksheet so it is using it as a calculated column. But I overrode and told it to turn off calculated column.

So it works great!