+ Reply to Thread
Results 1 to 7 of 7

LOOKUP / Retrieving Specific Cell (Auditing data)

  1. #1
    Registered User
    Join Date
    01-26-2011
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4

    LOOKUP / Retrieving Specific Cell (Auditing data)

    LOOKUP / Retrieving Specific Cell (Auditing data Need help)

    Not sure which formula to use and how to use it, but here is what I need help with:
    I have two worksheets in one workbook and both sheets contain names of individuals. One sheet contains a date. I need to audit the worksheet 1 to ensue all individuals have completed a form and insert the date that it was signed (which worksheet 2 captures). Example A provides a snapshot of how my cells are set-up:

    Example A:

    Worksheet 1 (entire population)
    Columns: A B C
    Headers: Date Signed User Name Displayed Name

    Sample Data (currently blank) Joe Smith Joe Smith
    James Smith James Smith
    Jo Jo Snail Joseph Snail

    Worksheet 2 (completed forms as of 1-25-11)
    Columns A B C
    Headers Name Title Date Signed

    Sample data Joe Smith Sr. VP 12/31/2010
    Joseph Snail Programmer 12/15/2010


    I am trying to do a VLOOKUP by comparing worksheet 1 and 2, and inserting the date signed in worksheet 1, but if there is no name in worksheet 2, then in worksheet 1 = false. See example B, as this is what I would like the end result to look like:

    Example B:

    Worksheet 1 (entire population)
    Columns: A B C D
    Headers: Completed Date Signed User Name Displayed Name

    Data: Yes 12/31/2010 Joe Smith Joe Smith
    No James Smith James Smith
    Yes 12/15/2010 Jo Jo Snail Joseph Snail

    Worksheet 2 (completed forms as of 1 25 11)
    Columns: A B C
    Headers: Name Title Date Signed

    Sample data Joe Smith Sr. VP 12/31/2010
    Joseph Snail Programmer 12/15/2010



    Please let me know if you have any questions or need clarification. Thanks for your help.
    Last edited by NBVC; 01-26-2011 at 05:26 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: LOOKUP / Retrieving Specific Cell (Auditing data)

    It is hard to see from your example how exactly the sheets are set up, but if you simply want to return a blank if no matches are found, then try something like:



    =IF(ISNUMBER(MATCH(C2,'Worksheet 2'!A:A,0)),VLOOKUP(C2,'Worksheet 2'!A:C,3,0),"")

    adjust sheetname and references to suit actuals.

    If still not right, post an actual workbook sample attachment and indicate your requirements.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    01-26-2011
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: LOOKUP / Retrieving Specific Cell (Auditing data)

    Hello - thank you for your quick reply. I would like to post an actual workbook sample but how do I upload one to this forum?

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: LOOKUP / Retrieving Specific Cell (Auditing data)

    When you reply , you should see a paperclip icon in the reply box, click it and browse and upload the workbook.

  5. #5
    Registered User
    Join Date
    01-26-2011
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: LOOKUP / Retrieving Specific Cell (Auditing data)

    Hello,

    I have attached a sample workbook with comments inserted in the cells for what I need help with.

    Thank you kindly for your help.
    Attached Files Attached Files

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: LOOKUP / Retrieving Specific Cell (Auditing data)

    In A2 enter:

    =IF(ISNUMBER(MATCH(C2,'Completed forms as of 1 25 11'!A:A,0)),"Yes","")

    copied down

    In B2 enter:

    =IF(A2="Yes",INDEX('Completed forms as of 1 25 11'!C:C,MATCH(C2,'Completed forms as of 1 25 11'!A:A,0)),"")

    copied down

    You may have to format this column as Date.

  7. #7
    Registered User
    Join Date
    01-26-2011
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: LOOKUP / Retrieving Specific Cell (Auditing data)

    You are a genius! Thank you so much for all your help! It worked!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1