Search and Insert txt from external file (Excel 2002)

Topic

New Reply

Hi

I have a spreadsheet with a sample list of personnel numbers and another file with the personnel number, users name and managers name. What I want to do is to search the full list file and insert the details into the sample list file for each personnel number in the sample file.

This is not a once off requirement so if anyone has any ideas I would appreciate it.

Thanks
Brendan

Reply | Quote

Replies

Looks like vlookup might be the function you need to do the work.
The Help thing is acually quite helpful (on this occasion).

Reply | Quote

You could try a Index/Match type of command. It would look something like
=index(‘[Personel Info.xls]sheet1!A$1$D$200,match(a2,Personel Info.xls]sheet1!A$1$A$200,),match(“User Name”,Personel Info.xls]sheet1!A$1$D$1,)
The A2 cell is the Personnel Number you want to search for and “User Name” is the exact name of the coloumn that you want to find. This checks down col A to find a match for your Personnel Number and returns a row number, then searchs row 1 (or where ever your rows are located) and returns a coloumn number. The Index command then looks at the row and coloumn and returns the value in the cell the rest of the command is pointing too.
If I haven’t explained myself well enough and you find it confusing let me know and I’ll attach an example…I’m running late right now and don’t have time to put one together, but it is easier than what I’ve made it out to be.

HTH
Stats

Reply | Quote

Hi Stats

I was wondering if you could give me an example of the index command and how it would read from an external file, as you may have gathered by now my excel skills are a bit weak. Thanks for the help.

Reply | Quote

For some reason known to Bill Gates alone, the Lookup Wizard won’t work if the data is in another workbook. So you have to type in the formula.
In the attached file I put the data in Sheet2 and the formulas are on Sheet1. When you’re entering your equation type in
“=index(” without the quotations then move to the workbook that contains the data and select the range you need including the headings of each column. In my formula I use “Match(a2,Sheet2!$a$1:$A$4,)”. This looks for the value of A2 down the A column in Sheet2. Then I use “Match(“Name”,Sheet2!$A$1:$C$1,)”. This looks for “Name” along the first row and combines the row and column to give you the result.
If you’re still having trouble feel free to email me the two files (I don’t know if you can attach two files in a post but you can send me private email) and I’ll be happy to put it together for you. I think it’s a lot easier to learn how to do something if I have a relevant example in front of me.
Best of Luck
Stats

Reply | Quote