Cell Formatting

[Papi]

In cell G8 and below there are numbers that need to be entered as below so if 12345 were entered it would return LPO-012345. The twist is there is a code that needs to come from each row eg. cell D8 might have "MECH" which should make cell G8 return LPO-MECH-012345. Given the number goes into the cell first is this even possible?

"LPO-"000000

 

[NeonRedSharpie]

I don't think you can use a cell reference in custom formatting. You could write a vba macro to add in the cell in D8 on worksheet change. That would be:

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case Target.Column
        Case 4, 7
            If Cells(Target.Row, 4) = "" Then
                Cells(Target.Row, 7).NumberFormat = """LPO-""000000"
            Else
                Cells(Target.Row, 7).NumberFormat = """LPO-""" & Cells(Target.Row, 4) & """-""000000"
            End If
        Case Else
    End Select


End Sub

Right click on your sheet, select "View Code", and paste this bad boy in there.

 

[Papi]

Congratulations making your 1,000+ Posts today!

I placed the code in and if nothing in D8 it defaults to LPO-001234 which is good. However when there is text in D8 eg. MECH and it returns a 1004 error but if a number like 999 is entered it returns LPO-999-001234. I tried =left(A8,4) in cell D8 as that is where the four digits come from eg. MECHANICAL but that did not work either. I'm not sure what it is doing.

 

[Papi]

bump...Can somebody see what would stop this from adding text from cell D8? It will include a number but not text.