Introduction

In this application, we will create an order processing worksheet for a car repair shop. We will first create a raw spreadsheet. Once we know it works fine, we will pay attention to its aesthetic aspects.

The company keeps a list of vehicle parts on a spreadsheet. When the user types a part number in a cell, we will use the VLOOKUP() function to retrieve the part name and its unit price, then calculate its sub total. We will also use a section where the jobs that were performed on a car can be recorded.

Practical Learning: Creating the Application

1. Start Microsoft Excel with its default file or create a new workbook
2. In Sheet1, create the workbook as follows:
    
   
3. Format cells G14:G21, I14:I21, I24:I29, I31, I32, I34, and I35 for Currency
4. Save the workbook as CPAS
5. Double-click the Sheet1 tab to put it into edit mode
6. Type Order Processing and press Enter
7. Double-click the Sheet2 tab to put it into edit mode
8. Type Vehicle Parts and press Enter
9. Still in the Vehicle Parts worksheet, create a list of parts
    
   or
10. Select all cells that have data in that worksheet, from cell A2 to D67
11. On the main menu, click Insert -> Name -> Define...
12. Type Parts as the name of the range and click OK
13. Save the workbook and click the Order Processing tab
14. Once the user has entered a part number under the Part # cell, we will find out if that part exists in our list and fill the other cells with its information (the part name and its price).
    Therefore, in cell D14 (the first empty cell under Part Name), type
    =IF(ISBLANK(C14), " ", VLOOKUP(C14, Parts,2,FALSE)) and press Enter
15. Drag the fill handle of cell D14 down to cell D21 to copy the expression
16. In cell G14 (the first empty cell under Unit Price), type
    =IF(ISBLANK(C14), " ", VLOOKUP(C14, Parts,3,FALSE)) and press Enter
17. Drag the fill handle of cell G14 down to cell G21 to copy the expression
18. In cell I14 (the first empty cell under Sub Total), type
    =IF(ISBLANK(H14), "", G14*H14) and press Enter
19. Drag the fill handle of cell I14 down to cell H21 to copy the expression
20. In cell I31, type =SUM(
21. Select cells I14:I21 and press Enter
22. In cell I32, type =SUM(
23. Select cells I24:I29 and press Enter
24. In cell I34, type =(I31+I32)*I33 and press Enter
25. In cell I35, type =I31+I32+I34 and press Enter
26. Save the workbook
27. Process an order by entering the following values:
     
    
28. Now that the worksheet is working fine, there are various or many things you can do to improve it.
    You can design the top section to display a better looking title
    You can (and should) protect the cells in which the user will not need to enter any information and unprotect those that will receive input
    You can format the cells borders to create a more professional worksheet and then remove the gridlines
    Here is an example of a design
     
    
29. Save and close the workbook