CODEWITHSUNDEEP
excelformulaprefix
D-Pep617
D-Pep617

Reputation: 11

Excel - Variable Prefix

I am trying to create a prefix to any numerical amount entered into a cell. The key here is that the prefix needs to be able to changed by updating a number in one "entry" cell. I have a chart of accounts for a budget, the chart of accounts will be the same, however with each file (i.e. new budget) the property has a unique identifier that will need to be the prefix to the account numbers.

Example Property Number: 700

Account 1: 3000 Account 2: 3100 Account 3: 3200

Lets say the property number (700) is in Cell A1, and the account numbers will be entered into cells A3, A4, A5, etc all the way down. When I enter "3000" in cell A3, I want "700-" to be displayed before it: 700-3000. Then if i change the amount in cell A1 (the property number) from 700 to 800 I want all of the account numbers to now have the "800-" before them... 800-3000

Looking for a non-VBA option if possible.

Any assistance would be greatly apprecated!

Upvotes: 0

Views: 416

Answers (2)

John Coleman
John Coleman

Reputation: 52008

A VBA Answer. In the code module for Sheet1 (or whatever sheet you want this for) try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Row > 2 Then
        If IsNumeric(Target.Value) Then
            Target.Formula = "=Concatenate(A1,""-""," & Target.Value & ")"
        End If
    End If
End Sub

If any number is entered in column A starting with the third row, that number is automatically converted to a formula which depends on cell A1

Upvotes: 0

John Coleman
John Coleman

Reputation: 52008

Looks like a job for Concatenate. In B3 enter

=CONCATENATE($A$1, "-",A3)

And copy down

Upvotes: 1

Related Questions