Invoice numbering?

[ljubo_gr]

Please, dear Excel super master users.
I have three(1.2.3) sheets, columns A and B on all three. In column B should be invoice number generated inserting data in A column, like, when typing "Bill" in A1, that should result MRE-INV-01 in B1. BUT, when typing "Jelen" in A1 on Sheet2, should be MRE-INV-02. and so on.
Invoice numbering should be generated by vlookup, Sheet1 to vlookup on sheet2&3, Sheet2 to vlookup sheet1&3... Then, probably IF function,,,, i don't know

 

[AlphaFrog]

And MAX not working showing only counted numbers(1,2,3,4,..). And i made mistake, invoice numbers should start like: INV-1-16, INV-2-16....

Right click on C1 and change the cell format. But now you want a different invoice format, so I will tell you what to change C1 format to once I figure out what you want.

I don't follow this; INV-1-16, INV-2-16
Explain.

 

[ljubo_gr]

INVOICE-NUMBER-YEAR (INV-1-16, INV-2-16, INV-3-16....)
MAX is Ok. i just added "INV-"&MAX&"-16"
I didn't try. So UNDO is Ok for other columns?

 

[ljubo_gr]

.NumberFormat = """INV-""0" "-""16"""

Oh, how?? maybe """INV-""0" &""-""&""16""

 

[ljubo_gr]

Actually, UNDO is Ok except A&B columns. But that's all right.
I need something even worse: INV-0/16, yes, forward-slash mark.

 

[ljubo_gr]

.NumberFormat = """INV-""0""/Moi""" This is working! Problem is in number 16!
i cannot add 16 to: Sheets("Sheet3").Range("B:B")) + 1 & ""/16""

 

[ljubo_gr]

I found a solution: """INV-""0""/16""" THANK YOU ALL!
THANK YOU AlphaFrog, Bill Jelen, Michael Girvin, Bill Szysz.....

 

[ljubo_gr]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Sheet1" Or Sh.Name = "Sheet2" Or Sh.Name = "Sheet3" Then
        If Target.Count = 1 Then
            If Target.Value <> "" Then
                If Target.Column = 1 Then
                    With Target.Offset(, 1)
                        .Value = Application.Max(Sheets("Sheet1").Range("B:B"), _
                                                 Sheets("Sheet2").Range("B:B"), _
                                                 Sheets("Sheet3").Range("B:B")) + 1
                        .NumberFormat = """INV-""439""/16"""
                    End With
                ElseIf Target.Column = 2 Then
                    If Application.CountIf(Sheets("Sheet1").Range("B:B"), Target) + _
                       Application.CountIf(Sheets("Sheet2").Range("B:B"), Target) + _
                       Application.CountIf(Sheets("Sheet3").Range("B:B"), Target) > 1 Then
                            MsgBox "Duplicate Invoice Number", vbExclamation, "Invalid Entry"
                            Target.ClearContents
                    End If
                End If
            End If
        End If
    End If
End Sub

This, eventually, is not working. Today is April, and i need to start from No 439 ("""INV-""439""/16"""). When i run code, it skips 439 and go on 441 ...449 and then 4410. I'm not the expert, but AlphaFrog code is probably based just on counting rows.

 

[ljubo_gr]

NO NO , i'm so sorry! It's working, ME DUMB!I just need enter manually first month entry(SalesNorth _ INV-439/16), and code work just fine!
I'm sorry & THANK YOU!

 

[ljubo_gr]

Need help, again.

File for download: https://onedrive.live.com/redir?resid=F86544C559394EB5!2434&authkey=!AAx2iDnylm2V0WA&ithint=file%2cxlsm

 

[ljubo_gr]

Need help, again.

File for download: https://onedrive.live.com/redir?resid=F86544C559394EB5!2434&authkey=!AAx2iDnylm2V0WA&ithint=file%2cxlsm

Please, help.