Excel spreadsheet formatting for sales orders
You can use a Microsoft Excel spreadsheet to create sales orders in Sales Order Entry. Create and format the spreadsheet, specifying the required header and line item information for an order, and then import the spreadsheet. An example of a spreadsheet is attached to KB article 1902953 on the Infor Support Portal.
Each spreadsheet in an Excel workbook represents a separate order. Each line on the spreadsheet is considered a separate line item on the order. All header-level columns and cells must be included in each sheet, even if the content is identical. If you do not format each spreadsheet correctly, an invalid import occurs.
Cell formatting
Cells that do not contain data but precede a cell with data must be unlocked. For example, if you specify a value in columns A-E and H in a row, you must unlock the cells in columns F and G. To unlock a cell, select Protection in the Format Cells window. You can unlock multiple cells simultaneously, but do not unlock all the cells in the worksheet. This causes an import error.
Check the number formatting for the cells. We recommend that you select Text because the data is displayed and imported as entered. If a cell has General or Number formatting and you enter a product number that begins with zero, the zero is eliminated. This might cause an import error, or cause the incorrect product to be included on the order.
Header formatting
The header information is contained in columns A and B, rows 1-7 of the spreadsheet.
This table shows the cells in the spreadsheet where you specify the header data:
| Row | Column A | Column B |
|---|---|---|
| 1 | Primary Key | Secondary Key |
| 2 | Customer # | Ship To |
| 3 | Warehouse | Order Type |
| 4 | Customer PO | Placed By |
| 5 | Ship Via | Terms |
| 6 | Reference | Requested Ship Date |
| 7 | Instructions |
You must specify Primary Key in
column A and Secondary Key in column B in row 1
of each spreadsheet. An invalid import occurs without these exact values. Customer # and
Warehouse are also required primary key values. If the customer number is invalid when
it is imported,**Invalid** is displayed in the Customer
# column. You must correct the customer number, and an incorrect warehouse, before you
can create a sales order. All other values are optional. If you do not specify values in
the remaining header cells, default values are used, based on the customer and
warehouse.
Line item formatting
The line item detail is contained in columns A-Y, rows 10-999 of the spreadsheet.
This table shows the cells in the spreadsheet where you can specify the line item data. Starting with column C, specify the value in the table as the column header. You must specify the value exactly as noted. An invalid import occurs without the exact values.
| Row | Column A | Column B | Column C | Column D | Column E |
|---|---|---|---|---|---|
| 1 | Primary Key | Secondary Key | Quantity | Unit | Price |
| 10-999 | <product> or Note or Comment | <description> or note text | <quantity> | <unit> | <price> |
| Row | Column F | Column G | Column H | Column I | Column J |
|---|---|---|---|---|---|
| 1 | Discount | Disc Type | Vendor | Product Line | Product Cat |
| 10-999 | <discount> | <discount type> | <vendor number> | <product line> | <product category> |
| Row | Column K | Column L | Column M | Column N | Column O |
|---|---|---|---|---|---|
| 1 | Product Cost | Tie Type | Tie Warehouse | Drop Ship Option | Print Option |
| 10-999 | <product cost> | <order tie type> | <warehouse> | <drop ship option> | Yes or Y, No or N, or blank |
| Row | Column P | Column Q | Column R | Column S | Column T | Column U |
|---|---|---|---|---|---|---|
| 1 | Print Invoice | Print Ack | Print TWL Pack | Print Delivery | Required Option | Sub Total Option |
| 10-999 | Yes or Y, No or N, or blank | Yes or Y, No or N, or blank | Yes or Y, No or N, or blank | Yes or Y, No or N, or blank | Yes or Y, No or N, or blank |
| Row | Column V | Column W | Column X | Column Y |
|---|---|---|---|---|
| 1 | Print Price Option | Copy Comments | National Program | Customer Product |
| 10-999 | Yes or Y, No or N, or blank | Yes or Y, No or N, or blank |
Only the product is required for each of the line items. If you do not specify values in the other cells, the information is derived from the appropriate records, based on the product.
Cross-reference logic
This standard cross-reference logic is supported when line items are imported:
- Customer product
- Barcode
- Supersede
- Interchange
- Catalog
- Auto pricing
- Manufacturers product
Cross references to the manufacturer's product are set up in Product Warehouse Product Setup. The other cross references in the list are set up in Product Extended Product Cross Reference Setup. A valid customer is required in the import file. If the cross reference is successful, the Requested Product field is updated with the original incoming part number, unless it is a customer product cross reference. In that case, the customer product number is loaded in to the Request Product field. A "C" is loaded in the Product Cross Reference Type field. If no customer product cross reference record is found for a customer that requires it, or multiple records are found, an error message is displayed for the line item. If the part number cannot be cross-referenced, it is imported as a nonstock product.
Notes
To add a note, specify Note in column A, the Product cell. Specify the note text in column B, the Description cell. The note can contain 1024 characters. To make the note required, specifyYes in column P, the Required Option cell. To print the note on the document, specify Yes in column O, the Print Option cell. If you add a note, ***NOTE*** is displayed in the Name field on the Import from Excel window. This indicates that the line is not a product line item.
Comments
To add a comment to a line item, specify COMMENT in column A, the Product cell, in the row directly below the line item. Specify the comment text in column B, the Description cell. The comment can contain 1024 characters, but only 48 characters are displayed in the Import to Excel window.
To add two comments to a line item, you must identify one of the comments as printable. Specify Yes in the Print Option cell. To copy the comments to a tied purchase order or warehouse transfer, specify Yes in the Copy Comment cell.
If the values in the Print columns (shown as Columns P-S) are all No, the Print Option selection is applied. If Print Option is Yes, line comments are printed on the Invoice and Acknowledgment documents only. A Value of Y or Yes is treated as YES, and any other value in these columns is treated as NO.
If you add a comment, ***COMMENT*** is displayed in the Name field on the Import To Excel window. This indicates that the line is not a product line item.