Exercise 28: Order form in Excel

Order form

How to create order form in Excel?

1.     Create product list, Format of Table (mark header box)

 

2.     Name product list – select product names (do not include title), name them ProductList

3.     Name product look up – select all table (do not include title), name them ProductLookup

4.     Create blank order form in new tab

5.     In Order Form tab: select product cell -> Excel’s Data tab: Data validation -> from allow choose List -> source: =ProductList -> OK 

6. Next, enter this formula in formula bar. Adjust PRODUCTCELL to your equivalent - in my case it was C14

=IF(PRODUCTCELL="","",VLOOKUP(PRODUCTCELL,ProductLookup,2,FALSE))

7. Create customer list, Format as Table

8.  Name customer list – select product names (do not include title), name them CustList

9.  Name customer look up – select all table (do not include title), name them CustomerLookup

10.  Enter this formula in formula bar. Adjust NAMECELL to your equivalent - in my case it was C6

=IF(NAMECELL="","",VLOOKUP(NAMECELL,CustLookup,2,FALSE))

12. To calculate total order use SUM function (how to use SUM function)