Thanks Max, I'll try it today and let you know if it does what I need.
"Max" wrote:
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,INDI RECT
("'"&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 <atyahoo<dotcom
----
"ExcelDummy" wrote:
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.
"Frank Kabel" wrote:
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
--
Regards
Frank Kabel
Frankfurt, Germany
ExcelDummy wrote:
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"??
|