Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I link information from a large table into new table
Hi, i hope I can explain this correctly in words.
What i'm trying to do is create a report that draws information from a large table. The report will only be drawing on some of the aspects of the larger table. I have tried the LOOKUP functions and they are not working in this case. Maybe there are too many values? For example there are many oranges, apples, and pears listed in the report, they have all different project numbers, start dates, reporting dates. I'm trying to produce a report on the oranges that list only the key information that I need from the massive table. I have tried the vlookup function with just the project numbers (i have typed theses into the new worksheet in hope it would this number to draw on the rest of the information I need from the table) but this does not work. Has anyone done this before and can help me? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I link information from a large table into new table
Lets start with a trivial example and see if that doesn't kick-start things.
Sheet2 (your list, simplified) A B 1 PRODUCT Units Sold 2 Apples 3 3 Oranges 5 4 Pears 4 5 Oranges 3 6 Tulips 1 7 Carrots 7 8 Lemons 4 9 Oranges 22 Over on 'Sheet1' you have A B 1 Oranges =SUMIF(Sheet2!A2:A9,A2,Sheet2!B2:B9) and the formula in B1 will show 30 in this case SUMIF says look in this list: Sheet2!A2:A9 for matches to the value in A2 and where matches are returned, return sum of related row values in the list at Sheet2!B2:B9 A1 on Sheet1 could be a list dropdown made up in several ways, or just typed in, but spelling must be same in A1 as it is in A2:A9 on Sheet2 list. Excel will also help you properly and easily set up a formula like the one in B1: click in B1 and type an = symbol followed by sumif( .... should look like =sumif( choose Sheet2 and highlight cells A2:A9 and type a comma then .... should look like =sumif(Sheet2!A2:A9, choose Sheet1 and click in A1 then type another comma .... should look like =sumif(Sheet2!A2:A9,A1, finally, choose Sheet2 again and highlight cells B2:B9 and hit the [enter] key. "Sarah - Sydney" wrote: Hi, i hope I can explain this correctly in words. What i'm trying to do is create a report that draws information from a large table. The report will only be drawing on some of the aspects of the larger table. I have tried the LOOKUP functions and they are not working in this case. Maybe there are too many values? For example there are many oranges, apples, and pears listed in the report, they have all different project numbers, start dates, reporting dates. I'm trying to produce a report on the oranges that list only the key information that I need from the massive table. I have tried the vlookup function with just the project numbers (i have typed theses into the new worksheet in hope it would this number to draw on the rest of the information I need from the table) but this does not work. Has anyone done this before and can help me? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting Information from A Pivot Table | Excel Discussion (Misc queries) | |||
How to link cell for value in another cell of pivot table? | Excel Discussion (Misc queries) | |||
Syntax Error - Using Microsoft Query and ODBC link to a table | Excel Discussion (Misc queries) | |||
Calendar based on varying information/ Pivot Table General | Charts and Charting in Excel | |||
I need help to link information such as name, address and phone t. | Excel Worksheet Functions |