lookup and transfer data
 

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

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

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

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=20

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

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

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=20

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=20
the 3 sheet names *exactly*)

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

To populate the table,

Put in B2:

=3DIF(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=20
as there are Part#s listed in col A

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

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

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

--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----
"ExcelDummy" wrote:
Each sheet is named by the info contained in it. Such=20
as "On hand Inventory",=20
"Requirements", "Sub-Contractors", etc. Each sheet has=20
Column ID's and I'm=20
wanting numeric values only to be transferred. Each sheet=20
may have the part=20
number listed if it has a value for the specific info in=20
the sheet. ie On=20
hand inventory, But the part number will not occur more=20
than once in each=20
sheet.=20

"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
=20
--=20
Regards
Frank Kabel
Frankfurt, Germany
=20
ExcelDummy wrote:
I have multiple spreadsheets that I want to move=20
certain information
from to get all my info into one spreadsheet. Is=20
there anyway to
search mutiple spreadsheets by a column like "Part=20
Number" and tell
it to retrive another column like "Qty" then post it=20
into a
corresponding column in another worksheet by=20
matching "Part Number"??

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

You're welcome !
--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----
"ExcelDummy" wrote:
Thanks Max, I'll try it today and=20
let you know if it does what I need.