Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search and Insert txt from external file (Excel 2002)

    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

  2. #2
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search and Insert txt from external file (Excel 2002)

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

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search and Insert txt from external file (Excel 2002)

    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

  4. #4
    New Lounger
    Join Date
    Nov 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search and Insert txt from external file (Excel 2002)

    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.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search and Insert txt from external file (Excel 2002)

    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •