lookup and transfer data

[Guest]

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"??

 

[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

 

[Guest]

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.

 

[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 B11 contains the 3 sheet names:
On hand Inventory,
Requirements,
Sub-Contractors

(Note: What's in B11 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
----

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.

matching "Part Number"??

 

[Guest]

Thanks Max, I'll try it today and let you know if it does what I need.

 

[Max]

You're welcome !