Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database Lookup both Horizontally & Verically (XP)

    I'm trying to create a report where I need the results in a cell to be the combination of looking up an ID number (ID numbers in my report are listed vertically in column A) and an assignment code (listed horizontally in row 5). I want the formula to look in the database (another Excel sheet in database format) and give me the mark for the that belongs to the particular ID for the particular assignment.

    How can I do this?

    Thanks in advance

    Christa

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Lookup both Horizontally & Verically (XP)

    Try using the Index function with two Match functions to supply the row and column parameters, Thusly
    Attached Images Attached Images

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Lookup both Horizontally & Verically (XP)

    Thanks...that works for a simple database. Mine is a little more complicated (I should have been more clear in my original post). Please see the attached file. This is just a small piece of my database (actual DB has 2773 rows). The database is the last sheet in the workbook. The report I want to get is the first sheet. What I would like is for the fromula to be able to look up the ID in column A of the report and the Assignment in row 5 (columns D onward) of the report, match it to the corresponding ID and Assignment in the database and there by pull out the corresponding values in the Grade_Text column.

    Thanks again in advance,

    Christa
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Database Lookup both Horizontally & Verically (XP)

    This can be done easily with a PIVOT Table
    Select data
    Data - pivot tablel report
    ID, SLP_GRP, LAST_FIRST as row
    REGISTRATION_ITEM as column
    Sum of GRADE_TEXT as Data

    Remove grand totals and subtotals
    Format as desired

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Lookup both Horizontally & Verically (XP)

    Thanks, Steve...

    I tried that and it comes out in the format I would like...however all the Grades come out as zeros... I've tried changing all the options I can see but can't seem to get it to work...any ideas?

    Thanks,

    Christa

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Database Lookup both Horizontally & Verically (XP)

    Your Grade_texts are entered as text. Probably due to an import.

    Highlight the column of data
    Data - text to columns
    Make sure delimited is marked
    Press <finish>

    Your text that looks like numbers are now numbers
    Steve

  7. #7
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Lookup both Horizontally & Verically (XP)

    Thank you! You've been a great 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
  •