+ Reply to Thread
Results 1 to 10 of 10

Data Extraction Exercise

  1. #1
    Forum Contributor
    Join Date
    06-20-2012
    Location
    Derby
    MS-Off Ver
    Excel 2010
    Posts
    381

    Data Extraction Exercise

    Hello,

    I need a little help retrieving data from one sheet and arranging it correctly into another.

    On the attached spreadsheet the first page will display a list of purchase order quantities by supplier and material.

    The material description is listed in A3.

    The data is in the sheet called 'Flight'. What I would like to happen is:

    On the 'Purchase Order Table' sheet column C shows a list of week numbers. On the 'Flight' sheet rows K5 to X5 show week numbers.

    K6 to X6 displays all the order quantities.

    I need a formula to look up the description in A3 on the 'Purchase Order Table' sheet, find it in column D of the 'Flight' sheet then look across and match the order quantities against the correct week number and then return the correct quantities against the correct week numbers in the 'Purchase Order Table'.

    Any ideas?

    I've mashed up a formula from a previous exercise but it's not worked at all!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Data Extraction Exercise

    Hi, You can try hlookup formula in D column of Purchase order table.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    06-20-2012
    Location
    Derby
    MS-Off Ver
    Excel 2010
    Posts
    381

    Re: Data Extraction Exercise

    Perfect. That's exactly what I need. Thankyou for your help

  4. #4
    Forum Contributor
    Join Date
    06-20-2012
    Location
    Derby
    MS-Off Ver
    Excel 2010
    Posts
    381

    Re: Data Extraction Exercise

    I've just done a little test on this formula and added another line into the Flight sheet with another material description and set of order quantities. When I alter the description in A3 on Purchase Order Table and ammended the lookup fields nothing happens. Is there something I need to put into the formula to read the description in A3?

    Thanks

  5. #5
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Data Extraction Exercise

    Ya. I didnt link the formula with A3. I think the material desc is available in Column D of flight sheet. So, the formula should be
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    06-20-2012
    Location
    Derby
    MS-Off Ver
    Excel 2010
    Posts
    381

    Re: Data Extraction Exercise

    Still having a little trouble with the formula. It's actually displaying the formula in the cell even after the D4: has been taken out...?

  7. #7
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Data Extraction Exercise

    That D4: is added for your reference - to place the formula in the cell. What is the trouble you are facing?

  8. #8
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Data Extraction Exercise

    Check for these..

    - Remove the space before = (if any)
    - Make sure cell format is 'General'
    - Ensure ther is no ' before the formula when you get into Edit mode by pressing F2
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  9. #9
    Forum Contributor
    Join Date
    06-20-2012
    Location
    Derby
    MS-Off Ver
    Excel 2010
    Posts
    381

    Re: Data Extraction Exercise

    Actually that's working perfectly now. Thanks again for you help. Appreciate it.

  10. #10
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Data Extraction Exercise

    Hi JakeMann

    You could have used the INDEX & MATCH you had:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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