Introduction

We will create a spreadsheet used to process orders for a car rental company. We will have a list of cars. During order processing, the user will type the tag number of a car, then the necessary information for that car will be retrieved and filled on the invoice. The rest will just be a routine.

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 the screenshot below this page
3. Format cells , I19, I20, I22, and I23 for Currency
4. Save the workbook as Bethesda Car Rental
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 Cars and press Enter
9. Still in the Vehicle Parts worksheet, create a list of cars
    
   
10. Select all cells that have data in that worksheet
11. On the Ribbon, click Formulas
12. In the Defined Names section, click Define Name...
13. Type Cars as the name of the range and click OK
14. Save the workbook and click the Order Processing tab
15. Once the user has entered a tag number, we will find out if its car exists in our list and fill the other cells with its information.
    Therefore, in cell C12 (the empty cell on the right side of Car Tag #), type
    =IF(ISBLANK(C11),"",VLOOKUP(C11,Cars,2,FALSE)) and press Enter
16. In cell G12, type
    =IF(ISBLANK(C11),"",VLOOKUP(C11,Cars,3,FALSE)) and press Enter
17. Drag the fill handle of cell G14 down to cell G21 to copy the expression
18. In cell C13, type
    =IF(ISBLANK(C11),"",VLOOKUP(C11,Cars,4,FALSE)) and press Enter
19. In cell I18, type =DAY(F16)-DAY(C16) and press Enter
20. Right-click cell I18 and click Format Cells...
21. In the Number tab and in the Category list, click General and click OK
22. In cell I20, type =I18*I19 and press Enter
23. In cell I22, type =I20*I21 and press Enter
24. In cell I23, type =I20+I22 and press Enter
25. Save the workbook
26. Now there are various ways you can improve the worksheet.
    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 with order processing
27. Save and close the workbook