How do you build purchase orders per vendor from a spreadsheet?

To build purchase orders from a spreadsheet reorder list, calculate an order quantity for every item, rounded up to the vendor's pack size, then give each vendor its own PO tab that uses FILTER to pull that vendor's items with a quantity above zero. Add the PO number, date and totals, save or print it as a PDF, and log it as on order.

How do you calculate how much to order?

Start from a reorder sheet with one row per item, like the one in the reorder point guide. Add three columns: a maximum (order-up-to level), the vendor's pack size and an order quantity.

1. Max (J2) = reorder point + daily sales × days between orders, for example =H2+E2*14 if you order every two weeks.
2. Pack size (K2): the case or carton quantity the vendor sells in. Use 1 for items sold singly.
3. Order quantity (L2): =IF(C2+D2<=H2, ROUNDUP((J2-C2-D2)/K2, 0)*K2, 0). It orders only when on hand plus on order has reached the reorder point, tops up to max, and rounds up to a whole pack.

Example: reorder point 30, max 58, 24 on hand, none on order, packs of 12. The gap is 34, which rounds up to 3 packs, so order 36.

Add a Unit cost column (M) and a line total in column N, =L2*M2, so each PO shows its value.

How do you split the list into one PO per vendor?

Give each vendor its own tab, with the vendor name in cell B1, and let FILTER pull that vendor's lines from the reorder sheet (here called Items).

• Excel: =FILTER(Items!A2:N500, (Items!B2:B500=B1)*(Items!L2:L500>0), "Nothing to order"). Multiplying the two conditions means both must be true. Microsoft lists FILTER for Excel for Microsoft 365, 2024 and 2021; in older versions, filter the Vendor column with AutoFilter and copy the rows instead.
• Google Sheets: =FILTER(Items!A2:N500, Items!B2:B500=B1, Items!L2:L500>0). Each extra condition is its own argument and must be the same length as the range.

Because the formula recalculates, the vendor tab is always current. Copy it and paste as values when you place the order, so the PO you send does not change afterwards.

What should each purchase order include?

• Your company name, ship-to address and a unique PO number, such as the date plus a sequence.
• Vendor name and the vendor's item numbers if they differ from yours.
• For each line: item, description, quantity, unit cost and line total.
• A PO total with =SUM() over the line totals, plus the requested delivery date.

Check the vendor's minimum order before sending. If the PO falls short, pull forward items that are close to their reorder point rather than over-ordering one item.

How do you send the PO and track it?

1. Excel desktop: select File > Save As (or Save a copy), choose PDF as the file type, and save. Google Sheets: select File > Print, choose Current sheet, and print or save the result.
2. Email the PDF to the vendor.
3. Add each line to an Open POs tab: PO number, vendor, item, quantity ordered, order date and quantity received.
4. Point the reorder sheet's On order column at that tab: =SUMIFS(OpenPOs!D:D, OpenPOs!C:C, A2) - SUMIFS(OpenPOs!F:F, OpenPOs!C:C, A2), so ordered stock stops triggering a second order.
5. When goods arrive, enter the received quantity and date. The dates also feed your lead time calculation.

What mistakes should you avoid?

• Not recording the PO. If On order is not updated, next week's sheet orders the same items again.
• Sending a live formula. Paste values before sending so later edits do not change a placed order.
• Reused PO numbers. Keep a running log so every number is used once.
• Ignoring pack sizes and minimums. Vendors round your order for you, often upward.

Can a tool build the POs for you?

ReorderOwl is one option. Upload your item list, sales history and optionally your open purchase orders, and ask what to reorder. When you tell it to create a purchase order, it builds one per vendor with the items, quantities and costs, in the chat and on the purchase order sheet of your workbook; you send it to your supplier. It works in ChatGPT today; Claude setup is coming shortly. See ReorderOwl for spreadsheets.

Frequently asked questions

How do I make one purchase order per vendor in Excel?

Give each vendor a tab and use FILTER to pull that vendor's rows with an order quantity above zero. FILTER needs Excel for Microsoft 365, 2024 or 2021.

Does Google Sheets have a FILTER function?

Yes. FILTER(range, condition1, condition2) returns rows that meet every condition; each condition must match the range's length.

How do I round an order up to a full case?

Divide the quantity needed by the pack size, round up with ROUNDUP, and multiply by the pack size again.

How do I stop ordering the same item twice?

Log every PO line on an Open POs tab and subtract what is on order before calculating the next order.

Can I save a Google Sheets PO as a PDF?

Use File, then Print, choose Current sheet, and print or save the result from the print window.

Sources

1. FILTER function, Microsoft Support, accessed 2026-10-01
2. FILTER function, Google Docs Editors Help, accessed 2026-10-01
3. Save or convert to PDF or XPS in Office desktop apps, Microsoft Support, accessed 2026-10-01
4. Print from Google Sheets, Google Docs Editors Help, accessed 2026-10-01
5. SUMIFS function, Microsoft Support, accessed 2026-10-01