Bethesda Car Rental


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
    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

Home Copyright 2004-2009 FunctionX, Inc.