excel order sheet

Question

hi, I have created an order sheet on excel, listing materials, with item cost and qty column.

on another tab I created an invoice. is it possible when I enter qty of material on the order sheet it can automatically update the invoice with material name, qty and cost on each line?

Answer 1

Hello @Rebecca,

Thank you for your question.

You may have already seen suggestions from Q&A Assist, which usually cover more advanced or flexible approaches depending on the final invoice requirements. That said, if you are looking for a simple and clean solution, you may want to try the approach below. It is easy to maintain and works well for basic order‑to‑invoice scenarios, though it may not cover all advanced cases.

Based on the structure you described, you can use Excel’s FILTER function to automatically display only the items that have a quantity entered on your order sheet.

In your "Order Sheet", you can start from row 2, where cell A2 contains the header “Material name”, column B contains the "Item cost, and column C contains the "Quantity".

Your "Invoice" sheet can start empty. On the "Invoice" sheet, in cell A2, enter the following formula:

=FILTER('Order Sheet'!A2:C10,'Order Sheet'!C2:C10>0,"")

This formula works as follows:

• The range 'Order Sheet'!A2:C10 is the source data that you want to display on the invoice.
• The condition 'Order Sheet'!C2:C10 > 0 tells Excel to include only rows where the quantity is greater than zero.
• The empty quotes at the end define what is shown if no items have a quantity entered.

Once this is in place, go back to the "Order Sheet" and enter quantities for any materials you want to include. When you switch to the "Invoice" sheet, you will see only those materials automatically listed, along with their cost and quantity.

This approach is intentionally simple, so whether it fully fits your needs depends on how complex you want the invoice to be later. However, it is a good starting point and often feels easier to manage if you prefer to avoid more advanced formulas or automation.

Please feel free to try it and let me know if this works comfortably for your scenario.

Kind regards,

---

If the answer is helpful, please click "Accepted" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Yes, this can be done by making the invoice sheet pull data from the order sheet instead of typing it manually.

One common approach is:

1. Give the order sheet a clear layout, for example:
   • Column A: Material name
   • Column B: Item cost
   • Column C: Quantity ordered
2. On the invoice sheet, create a table for line items, for example:
   • Column A: Material name
   • Column B: Quantity
   • Column C: Unit cost
   • Column D: Line total
3. Use lookup formulas on the invoice sheet so that when a material is chosen, the cost and quantity are pulled from the order sheet:
   • In the invoice “Material name” cell (say A10), either type or use a dropdown (Data Validation) that lists materials from the order sheet.
   • In the invoice “Quantity” cell (B10), use a lookup formula that returns the quantity from the order sheet, for example (classic VLOOKUP pattern):

          =IFERROR(VLOOKUP($A10,OrderSheet!$A:$C,3,FALSE),"")
     

     This assumes the order sheet is named OrderSheet and quantity is in column C.
   • In the invoice “Unit cost” cell (C10):

          =IFERROR(VLOOKUP($A10,OrderSheet!$A:$C,2,FALSE),"")
     

   • In the invoice “Line total” cell (D10):

          =IF($B10="","",$B10*$C10)
     

4. Copy these formulas down for as many invoice lines as needed. When quantity is entered or changed on the order sheet, the invoice sheet updates automatically.

If there are multiple orders or customers, the same pattern can be extended by adding an Order ID column and including it in the lookup key (e.g., using INDEX/MATCH or XLOOKUP) so each invoice pulls only the lines for that specific order.

---

References:

• Move data from Excel to Access
• Tutorial: Analyze sales data from Excel and an OData feed