Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2004
    Location
    Denver, Colorado, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pulling info from Worksheet A for records in B ??? (2000 9.0.3821)

    I'm using Excel to keep track of defunct users of an application I administer. I pulled all user names including Full Name, User Name and Description and dumped them into Excel. I then checked to see if each of these users were in Outlook. Those that were not I dumped into a seperate worksheet.

    Now what I want to do is, for each of the users not found in Outlook (Worksheet [img]/forums/images/smilies/cool.gif[/img] I want to find the same user in Worksheet A, together with the User Name, Descripton, etc. from Worksheet A for each defunct user.

    I'm completely stumped as to how to do this. Any help much appreciated.

    Worksheet A:

    Full Name User Name Description
    Joe Smith JSMITH JOE User
    Bill Nye BNYE Tics
    Fred Blog FBLOG Manager


    Worksheet B:
    Full Name
    Bill Nye

    - I need to be able to just pull the row for Bill Nye from Worksheet B. There are some 100 records in Worksheet B that I need information for from Worksheet A, which has 1000 or more records.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pulling info from Worksheet A for records in B ??? (2000 9.0.3821)

    Say that the situation in Worksheet A is like this:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td>Full Name</td><td>User Name</td><td>Description</td><td align=center>2</td><td>Joe Smith</td><td>JSMITH</td><td>JOE User</td><td align=center>3</td><td>Bill Nye</td><td>BNYE</td><td>Tics</td><td align=center>4</td><td>Fred Blog</td><td>FBLOG</td><td>Manager</td></table>
    You could use this in Worksheet B:

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><tr><td align=center>1</td><td>Full Name</td><td align=right>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pulling info from Worksheet A for records in B ??? (2000 9.0.3821)

    Put the two formulas below in the cells next to the name on Worksheet B then copy them down the column:

    [pre]
    =VLOOKUP(A1,'Worksheet A'!$A:$C,2,FALSE)
    =VLOOKUP(A1,'Worksheet A'!$A:$C,3,FALSE)
    [pre]
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    May 2004
    Location
    Denver, Colorado, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pulling info from Worksheet A for records in B ??? (2000 9.0.3821)

    Thanks for the fast response, Legare. This looks like a great forum. I've been a fan of Woody's since his Mother of all windows 95 book.

    I'll try the VLOOKUP formula. I'll just substitute 'Worksheet A' for the 'Actual name of my worksheet' or 'Sheet 1' ?

    Thanks again!

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pulling info from Worksheet A for records in B ??? (2000 9.0.3821)

    As you are looking for data on entries that do not exist in Workshhet B (if I am reading your situation correctly) , I think your formula needs to be in Sheet A. Assuming the columns you have indicated are A, B and C, in say D2 you could enter the following :

    <big>=IF(COUNTIF(Sheet2!A:A,A2)>0,"","Defunct")</big>

    and copy it down.

    That will highlight all entries in Worksheet A that do nat have a match in Worksheet B.

    Change Sheet names, cell address as appropriate.

    Andrew C

  6. #6
    New Lounger
    Join Date
    May 2004
    Location
    Denver, Colorado, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pulling info from Worksheet A for records in B ??? (2000 9.0.3821)

    Thanks guys! I was able to get it to work. After analysing the VLOOKUP formula (Lookup_value, Table_array, Col_index_num and Range_lookup) I think I get it. Thanks for your help!

Posting Permissions

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