Data Extraction Exercise

1. 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!

2. Re: Data Extraction Exercise

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

3. Re: Data Extraction Exercise

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

4. 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. 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:
`Please Login or Register  to view this content.`

6. 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. 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. 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

9. Re: Data Extraction Exercise

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

10. Re: Data Extraction Exercise

Hi JakeMann

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

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

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