How to Make two cells of information relate to each other

[Kbaile53]

I’m looking for a way to use a job number in coalescence with a job name for our time sheets. Basically the company we contract under organizes jobs by job number (ex. LP0068435). seeing as we are a much smaller company, our guys don’t care to memorize these numbers. we would just use general descriptions (Ex: Building 12 concrete demolition). However, We have to bill our time with that job number. Does anyone know what funtion(s) I can use merge these two pieces of information? so when one of my guys types “building 12 concrete demolition” into the spreadsheet, it will automatically replace it with “LP0068435”
Thanks for your time...

 

[Fluff]

A cell can hold a value or a formula, but not both.
You could create a lookup table of job numbers/descriptions & then use a Vlookup formula to put the job number in another cell.

 

[DanteAmor]

It can be with VBA code, you put the description in cell A2 on Sheet1 and press enter, at that moment the code is activated and it looks for the job number in sheet2, if it exists it replaces the description with the job number.

Ex.
<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:229px;" /><col style="width:229px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; text-align:center; ">DESCRIPTIONS</td><td style="background-color:#ffff00; text-align:center; ">JOB NUMBER </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Building 12 concrete demolition</td><td >LP0068435</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Building 5 Repair</td><td >PL1234567</td></tr></table> <br /><br />

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:239px;" /><col style="width:128px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; text-align:center; ">DESCRIPTIONS</td><td style="background-color:#ffff00; text-align:center; ">Bill time</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >LP0068435</td><td style="text-align:right; ">800</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Building 5 Repair </td><td style="text-align:right; ">600</td></tr></table> <br /><br />


SHEET EVENT
Right click the tab of the sheet1 to work, select view code & paste the code into the window that opens up.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        Dim sh As Worksheet
        Dim r As Range
        Set sh = Sheets("Sheet2")
        Set r = sh.Range("A:A").Find(Target.Value)
        If Not r Is Nothing Then
            Application.EnableEvents = False
            Target.Value = r.Offset(0, 1).Value
            Application.EnableEvents = True
        Else
            MsgBox "The description does not exist", vbExclamation
        End If
    End If
End Sub

Try and tell me.