M
Mary
I have 2 Workbook one name openpo.xls and one name
salesorder.xls. Below is the example of how they look. I
am using Vlookup Functions. The formula is in the
OPenpo.xls workbook. The formulas I am using are as follows:
(Customer ID)
F2 =VLOOKUP(A2,'[sales order.xls]Sheet1'!$A$2:$H$996,4,0)
(S.O. #)
G2 =VLOOKUP(A2,'[sales order.xls]Sheet1'!$A$2:$H$996,7,0)
(Total S.O. Amt)
H2 =VLOOKUP(A2,'[sales order.xls]Sheet1'!$A$2:$H$996,8,0)
Which works fine until I come to a S.O. that has more than
one P.O. I am exporting this from Crystal to Excel, using
delimited, coma. In the Sales Order Workbook when it is
exported the P.O. are listed in one column. Such as
1522,15223,15224 or 15008,15009. I insert 2 blank columns
first. Then I highlight the P.O. # field and select Data -
Text to Column.
Openpo.xls
P.O # P.O.Date Ven. qty ord. Qty Rec. Cust S.O.# Tot.Amt
15008 6/1/2003 Emtek 1.00 0.00 Rich 9470 3,943.09
15009 6/1/2003 Emtek 1.00 0.00 #N/A #N/A #N/A
15201 7/10/2003Holly 100.00 90.00 Storey 9649 8,413.62
15202 7/10/2003Holly 100.00 90.00 #N/A #N/A #N/A
15222 8/19/2003 Pas 1.00 0.00 Joan 9617 33,117.70
15223 8/19/2003 Pas 1.00 0.00 #N/A #N/A #N/A
15224 8/19/2003 Pas 1.00 0.00 #N/A #N/A #N/A
15278 8/19/2003 Pas 1.00 0.00 Gant 9726 5,822.73
15283 9/10/2003 Dyke 30.00 15.00 BCA 9691 16,557.08
15285 9/25/2003 Anders 1.00 0.00 Gates 9701 5,630.34
Salesorder.xls
PO# PO# PO# Cust. Emp. SO Date SO# S0. AMT
15008 15009 Rich Berry 8/7/03 9470 3,943.09
15201 15202 15203 Todd Berry 10/7/03 9649 8,413.62
15222 15223 15224 Joan Peck 9/29/03 9617 33,117.70
15278 Gant Peck 10/23/03 9726 5,822.73
15283 BCA Peck 10/17/03 9691 16,557.08
15285 Gates Peck 10/17/03 9701 5,630.34
15304 Gant Peck 10/24/03 9725 4,775.41
salesorder.xls. Below is the example of how they look. I
am using Vlookup Functions. The formula is in the
OPenpo.xls workbook. The formulas I am using are as follows:
(Customer ID)
F2 =VLOOKUP(A2,'[sales order.xls]Sheet1'!$A$2:$H$996,4,0)
(S.O. #)
G2 =VLOOKUP(A2,'[sales order.xls]Sheet1'!$A$2:$H$996,7,0)
(Total S.O. Amt)
H2 =VLOOKUP(A2,'[sales order.xls]Sheet1'!$A$2:$H$996,8,0)
Which works fine until I come to a S.O. that has more than
one P.O. I am exporting this from Crystal to Excel, using
delimited, coma. In the Sales Order Workbook when it is
exported the P.O. are listed in one column. Such as
1522,15223,15224 or 15008,15009. I insert 2 blank columns
first. Then I highlight the P.O. # field and select Data -
Text to Column.
Openpo.xls
P.O # P.O.Date Ven. qty ord. Qty Rec. Cust S.O.# Tot.Amt
15008 6/1/2003 Emtek 1.00 0.00 Rich 9470 3,943.09
15009 6/1/2003 Emtek 1.00 0.00 #N/A #N/A #N/A
15201 7/10/2003Holly 100.00 90.00 Storey 9649 8,413.62
15202 7/10/2003Holly 100.00 90.00 #N/A #N/A #N/A
15222 8/19/2003 Pas 1.00 0.00 Joan 9617 33,117.70
15223 8/19/2003 Pas 1.00 0.00 #N/A #N/A #N/A
15224 8/19/2003 Pas 1.00 0.00 #N/A #N/A #N/A
15278 8/19/2003 Pas 1.00 0.00 Gant 9726 5,822.73
15283 9/10/2003 Dyke 30.00 15.00 BCA 9691 16,557.08
15285 9/25/2003 Anders 1.00 0.00 Gates 9701 5,630.34
Salesorder.xls
PO# PO# PO# Cust. Emp. SO Date SO# S0. AMT
15008 15009 Rich Berry 8/7/03 9470 3,943.09
15201 15202 15203 Todd Berry 10/7/03 9649 8,413.62
15222 15223 15224 Joan Peck 9/29/03 9617 33,117.70
15278 Gant Peck 10/23/03 9726 5,822.73
15283 BCA Peck 10/17/03 9691 16,557.08
15285 Gates Peck 10/17/03 9701 5,630.34
15304 Gant Peck 10/24/03 9725 4,775.41