College Park Auto Parts

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

 

 


Home Copyright 2004 FunctionX, Inc.