Auto Populating a cell when a value is entered

[stevembe]

Hi,

I have a spreadsheet in a workbook (Excel 2003) that lists cost centres by department. The cost centre is a three figure number with the department description next to it e.g.

123 Human Resources
456 Warehouse

In a different worksheet the details of work hours are recorded but what I want to happen is in the seperate spreadsheet if I type 123 the adjacent cell will auto populate with Human Resources. At the moment I am constantly referring back manuall to the first spreadsheet to see what 123 is as a department. It is a bit of pain as there are hundreds of cost centres.

Any help greatly appreciated

Steve

 

[Sektor]

This is Worksheet_Change event.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim i As Long
    Dim arr(1 To 2, 1 To 2) As Variant
    
    arr(1, 1) = 123
    arr(1, 2) = "Human Resources"
    arr(2, 1) = 456
    arr(2, 2) = "Warehouse"
    
    For i = 1 To UBound(arr, 1)
        If arr(i, 1) = Target Then
            Target = Target & " " & arr(i, 2)
        End If
    Next

End Sub

It's just example. In reality, you can add special sheet with two columns: digits (column A) and keywords (column B). Then the code would look like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim i As Long
    Dim arr As Variant
    
    arr = Sheets("keywords").Range("A1").CurrentRegion
    
    For i = 1 To UBound(arr, 1)
        If arr(i, 1) = Target Then
            Target = Target & " " & arr(i, 2)
        End If
    Next

End Sub

 

[Lionround]

You could use VLOOKUP

Make two columns
N O
1 123 DepartmentA
2 234 DepartmentB
3 345 DepartmentC

In the cell you want to Auto Populate
=VLOOKUP(FirstCellYouWantToPopulateWithData,N1:O3,2)

Replace N1:O3 with your table range. The "2" is I want data from the second column.

HTH

 

[CharlesChuckieCharles]

A vlookup should suffice.

open both spreadsheets

next to the cell you type 123 into Create a Vlookup
as in b1
=Vlookup(A1
Then type ,
the move to the spreadsheet with the names list
highlight the number and name columns, you will see the Vlookup looking like

=VLOOKUP(A1,[lookupInterest.xlsx]Sheet2!$A$1:$B$18

Then
Then type ,
then 2 (for column two which has the names)
Then False (only exact match wanted)
then )

It should work

You can now close your name list spreadsheet

and the vlookup will still work

Magic!

in your work sheet the vlookup will look something like

=VLOOKUP(A17,'P:\My Documents\[lookupInterest.xlsx]Sheet2'!$A$1:$B$18,2,FALSE)

 

[Lionround]

Charles' reply is better than mine

 

[stevembe]

=VLOOKUP(A17,'P:\My Documents\[lookupInterest.xlsx]Sheet2'!$A$1:$B$18,2,FALSE)[/QUOTE]

Thank you Charles but I am a little confused. Both spreadsheets are in the same workbook and I have tried to follow your instructions but I have failed. The values are in sheet 1 of the workbook, Columns A and B. The numbers are in Column A and the Department in Column B. In sheet 2 Column A if I type 123 into A1 for example I want B1 to autopopulate with HUMAN Resources.

Sorry, I am not such a whizz with Excel!

 

[Lionround]

Steve,

Try this

=VLOOKUP(A1, Sheet1$A$1:$B$18,2,FALSE)

$B$18 Change the "18" to whatever the last number is in the column where the names are NOW.

 

[stevembe]

Steve,

Try this

=VLOOKUP(A1, Sheet1$A$1:$B$18,2,FALSE)

$B$18 Change the "18" to whatever the last number is in the column where the names are NOW.

Thanks but it just comes back as formula contains an error. What cell should I be placing the formula in?

 

[Lionround]

The formula goes into the cell that you want to populate with the name.

 

[Lionround]

Column A if I type 123 into A1 for example I want B1 to autopopulate with HUMAN Resources.

In your example above, you would post the formula into B1 on Sheet 2.