lookup and transfer data

E

ExcelDummy

I have multiple spreadsheets that I want to move certain information from to
get all my info into one spreadsheet. Is there anyway to search mutiple
spreadsheets by a column like "Part Number" and tell it to retrive another
column like "Qty" then post it into a corresponding column in another
worksheet by matching "Part Number"??
 
F

Frank Kabel

Hi
some questions upfront:
- how are your sheets named exactly
- columns IDs
- Are you searching for a numeric value only
- Is there only one occurence of each part number
 
E

ExcelDummy

Each sheet is named by the info contained in it. Such as "On hand Inventory",
"Requirements", "Sub-Contractors", etc. Each sheet has Column ID's and I'm
wanting numeric values only to be transferred. Each sheet may have the part
number listed if it has a value for the specific info in the sheet. ie On
hand inventory, But the part number will not occur more than once in each
sheet.
 
M

Max

One way to try ..

Assuming you have this kind of set-up

In sheet: Sub-Contractors
In cols A and B, data from row2 down

Part# Qty
1111 1234
1112 2345
1113 3456
1114 4567
1115 5678

In sheet: On hand Inventory
In cols A and B, data from row2 down

Part# Qty
1111 100
1112 200
1113 300
1114 400
1115 500

In sheet: Requirements
In cols A and B, data from row2 down

Part# Qty
1111 900
1112 1000
1113 2000
1114 3000
1115 4000

Then, if you want the summary table
In sheet: QtySummary
In cols A and B, data from row2 down

Part# On hand Inventory Requirements Sub-Contractors
1111 100 900 1234
1112 200 1000 2345
1113 300 2000 3456
1114 400 3000 4567
1115 500 4000 5678

where B1:D1 contains the 3 sheet names:
On hand Inventory,
Requirements,
Sub-Contractors

(Note: What's in B1:D1 must match
the 3 sheet names *exactly*)

And in A2 down will be the list of Part#s ..

To populate the table,

Put in B2:

=IF(ISNA(MATCH($A2,INDIRECT("'"&B$1&"'!
A:A"),0)),"",INDIRECT("'"&B$1&"'!B"&MATCH($A2,INDIRECT
("'"&B$1&"'!A:A"),0)))

Copy across to D2, fill down as many rows
as there are Part#s listed in col A

The above will return the the values in the Qty col
from the 3 sheets corresponding to the Part#s
in col A.

Unmatched items, if any, will return blanks: ""

For the sample data in the 3 sheets,
you'll get the resulting summary table above

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
ExcelDummy said:
Each sheet is named by the info contained in it. Such as "On hand Inventory",
"Requirements", "Sub-Contractors", etc. Each sheet has Column ID's and I'm
wanting numeric values only to be transferred. Each sheet may have the part
number listed if it has a value for the specific info in the sheet. ie On
hand inventory, But the part number will not occur more than once in each
sheet.
Click to expand...
matching "Part Number"??
 
Top