How to display different values to ones entered

[jmuzz0630]

Good morning all,

I am after some assistance with the following:

Data I enter into my worksheet cells is a 4-digit number which is the vehicle registration number (numbers only, no letters). The data I want displayed in the same cell is the fleet number of the vehicle, not the registation number.

There is approx 100 vehicles that have fleet numbers, so going back & forth trying to correspond the registration number with the fleet number is a time consuming task that has high potential for human error.

I am after a formula or something similar that can assist me, so when I enter the registration number, somehow excel can correspond that rego number with the fleet number, and display the fleet number in that cell.

For example (for this example, Rego 1234 = Fleet 50):
Cell A1, I enter "1234". . I then want Cell A1 to display "50" instead of "1234" without any human input entering "50"...

Does this all make sense, or am I dreaming of such a function??

Thank you in advance!

 

[SanjayGMusafir]

What I can think from that you explained is

• Creating a list and
• Ideally that list should have 2 components 4 digit number followed by Fleet number - Check column C below
• Using that list for data validation

Book1
	A	B	C
1	Number	Fleet No	Final
2	1085	abcdefgh	1085 - abcdefgh
3	1172	bcdefghj	1172 - bcdefghj
4	2661	xyzopnm	2661 - xyzopnm
5	3183	tryanothert	3183 - tryanothert
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=TEXTJOIN(" - ",TRUE,A2:B2)

 

[kevin9999]

I would do it this way.
Create a lookup table with all possible registration numbers, with their respective fleet numbers in the adjacent column. Then, add a Worksheet_Change macro to the sheet where you're entering the registration numbers (right-click the sheet tab name, select View Code and place the code in the window that appears on the right of screen). That way, whenever you enter a registration number in column A - it will automatically change to the associated fleet number in the same cell. I would further avoid potential errors by having the cells in column A tied to a validation list - being the list of all possible registration numbers in the lookup table.
Sheet looks like this:

Rego to Fleet number.xlsm
	A	B	C	D	E
1				Rego#	Fleet#
2				1234	50
3				2345	50
4				3456	60
5				4567	60
6				5678	70
7				6789	70
8				7890	80
9				0123	80
10
11
12
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A1:A12	List	=$D$2:$D$9

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("A:A"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Dim s As String, c As Range
        s = Target
        Set c = Columns("D:D").Find(s)
            If c Is Nothing Then
                MsgBox "Registration not found"
                GoTo Continue
            Else
                Target = c.Offset(0, 1)
            End If
        End If
Continue:
        Application.EnableEvents = True
        Exit Sub
Escape:
        MsgBox "Error " & Err.Number & ": " & Err.Description
        Resume Continue
End Sub

Link to demo file: Rego to Fleet number.xlsm