Bethesda Car Rental

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 follows:
     
  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, from cell A2 to O40
  11. On the main menu, click Insert -> Name -> Define...
  12. Type Cars 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 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
  15. In cell G12, type
    =IF(ISBLANK(C11),"",VLOOKUP(C11,Cars,3,FALSE)) and press Enter
  16. Drag the fill handle of cell G14 down to cell G21 to copy the expression
  17. In cell C13, type
    =IF(ISBLANK(C11),"",VLOOKUP(C11,Cars,4,FALSE))
    and press Enter
  18. In cell I18, type =DAY(F16)-DAY(C16) and press Enter
  19. Right-click cell I18 and click Format Cells...
  20. In the Number tab and in the Category list, click General and click OK
  21. In cell I20, type =I18*I19 and press Enter
  22. In cell I22, type =I20*I21 and press Enter
  23. In cell I23, type =I20+I22 and press Enter
  24. Save the workbook
  25. 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
  26. Save and close the workbook
 


Home Copyright 2004 FunctionX, Inc.