Automated Invoice in Excel

This article talks about how to automate invoice creation in Excel.

This is what the invoice looks like. If you select a customer number from the drop-down list in a cell E6, Excel will automatically populate the table with customer information. If you select the serial number of the product in one of the drop-down lists of the range A13:A31, Excel will automatically fill in the product information.

Next, we will describe how we automated the input of product information. The same techniques were used to enter customer information.

1. Enter product information on the sheet Products.
2. Select a range of cells A13: A31 on the sheet Invoice.
3. On the Advanced tab Data (Data) click command Data Validation (Data validation).
4. Выберите List (List) from the drop-down list Allow (data type).
5. Place the cursor on the line Source (Source) and select range A2: A5 on the sheet Products.
6. Manually change 5 to 1048576 (or any other large number) to add more cells. Now you can enter as many new products as you need.
7. Press OK.
8. Highlight a cell B13 and enter the formula shown below:

   =IF(ISBLANK(A13),"",VLOOKUP($A13,Products!$A:$C,2,FALSE))

   =ЕСЛИ(ЕПУСТО(A13);"";ВПР($A13;Products!$A:$C;2;ЛОЖЬ))

Explanation: If the cell A13 is empty, the formula returns an empty string. If not, the function VLOOKUP (VLOOKUP) looks for the product serial number (1001) in the leftmost column of the range $A:$C list Products and returns the value in the second column of the same row.

9. Select cell С13 and enter the formula shown below:

   =IF(ISBLANK(A13),"",VLOOKUP($A13,Products!$A:$C,3,FALSE))

   =ЕСЛИ(ЕПУСТО(A13);"";ВПР($A13;Products!$A:$C;3;ЛОЖЬ))

Explanation: This formula is almost the same as the previous one. But this time it returns the value in the third column of the same row.

10. Highlight a cell E13 and enter the formula shown below:

    =IF(ISBLANK(A13),"",C13*D13)

    =ЕСЛИ(ЕПУСТО(A13);"";C13*D13)

Explanation: If the cell A13 is empty, the formula returns an empty string. If not, the product of price and quantity.

11. To copy formulas to other cells, select a range C13:E13 and drag it down to the line 31… Use Format Painter (Format Painter) to restore formatting.