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

 

[jasonb75]

Methods to perform tasks like this in excel are usually based on the count of records above the current row, sorting the data means that this happens with the post sort position, not pre sort position.

If you have another column that contains date and time, then you could perhaps use that as part of a formula, other than that you would need to use vba.

 

[Pestomania]

I am okay with using VBA if that is what I need to do. I can build into the VBA to run only when a specific field is changed. I just wasn't sure how to build that in VBA.

 

[jasonb75]

I would use a worksheet change event on the department column to write the formula then convert it to value.
If you need me to write it for you then I need the correct columns and the vlookup part of the current formula so that I can write the correct ranges to the code.

 

[Pestomania]

Hi. I will provide anything that I can. Unfortunately, I just cannot figure out what to do.

So the columns are on:

Sheet Name: -NEW- Kanban Designs
Cells: A1:B2
They formula would be looking at Column A to determine column B.
The abbreviations would be pulled from:

Sheet: All Validation Tables
Table Name: Table_Department
Cells: A1:B21

Here is the setup for the validation table:

Department	Abbreviation
Contracts	CNTR

 

[jasonb75]

I think that this should do it, this needs to go into the worksheet module (right click tab > view code) for the sheet: -NEW- Kanban Designs

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

Note that this will only work with new (single) items added to the list, not existing items or bulk additions from copy and paste.

 

[Pestomania]

Hi. I think I added it correctly, but it still isn't activating. Below is an image of what I did.

 

[jasonb75]

It needs to go in the worksheet module, not a regular module.

In your screen capture, double click on Sheet3 (-NEW- Kanban Designs) then paste the code into that module.

 

[Pestomania]

Hi Jason,

I went through the debugger. It was activating but it is not recognizing the line that i have pointed an arrow at. It is believe something is in that field and going to "else".

Here is the drop down for the line in question

 

[jasonb75]

Oops, my bad. It was looking at the wrong column, think this has fixed it

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 With
    End With
End With
End Sub