Cell Formatting

Papi

Well-known Member
Joined
May 22, 2007
Messages
1,592
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?

Code:
"LPO-"000000
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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:

Code:
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

Well-known Member
Joined
May 22, 2007
Messages
1,592
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.
 
Last edited:

Papi

Well-known Member
Joined
May 22, 2007
Messages
1,592
bump...Can somebody see what would stop this from adding text from cell D8? It will include a number but not text.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,205
Messages
5,594,830
Members
413,942
Latest member
Dhornsby21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top