Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Consolidating data from different workbooks
I have 3 different workbooks. They all have part numbers in one column and
qty in another. I need to find part numbers from one worksheet that are imbedded in the other 2. Then I need to total the qty for each part number. Any help is appreciated. |
#2
|
|||
|
|||
Hi
you can use the VLOOKUP function to lookup the part numbers in the different books and return the values to your first workbook for summing. E.g. with Book1 open and the part number you're looking for in column A and the quantity in that book in column B, in column C use a formula such as =IF(ISNA(VLOOKUP(A2,'[book2.xls]Sheet1'!$A$2:$B$100,2,0)),0,VLOOKUP(A2,'[book2.xls]Sheet1'!$A$2:$B$100,2,0)) which says look for the value in A2 in Sheet1 of Book2 (column A) and return the related quantity from column B where there is an exact match - otherwise return 0. in column D do the same for the other workbook now in column E you can do a simple =SUM(B2:D2) formula - these formulas can then be copied down for all your records. Cheers JulieD PS alternatively you could look at using Pivot Tables, taking data from multiple consolidation ranges. check out http://www.contextures.com/xlPivot08.html for details. "deadsxy692003" wrote in message ... I have 3 different workbooks. They all have part numbers in one column and qty in another. I need to find part numbers from one worksheet that are imbedded in the other 2. Then I need to total the qty for each part number. Any help is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Should Merging workbooks pick up new data or only edited data? | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Automatic updating worksheet data between different workbooks | Excel Worksheet Functions | |||
Merging data from several workbooks | Excel Worksheet Functions | |||
Controlling Linked Data to Workbooks | Excel Worksheet Functions |