Ranch House Assignment WalkthroughsEstimating Basics Assignment WalkthroughsAssignment Walkthroughs
  • Customer Database Walkthrough

    • Customer Database Walkthrough

    The Estimate Worksheet is a living document that is used by multiple people involved with the project being estimated. Not every tab in the worksheet may be relevant to each of those involved persons, so having a database to pull information from for tabs will save time over manually entering the information. For this purpose, you will create databases to use with data validation to create an easy way to ensure correct information is being used in the relevant sections of the Estimating Worksheet.

    Table of Contents

    Assignment Overview

    Customer Database Scope of Work

    Your complete assignment should meet the following criteria:

    CustomerDB Tab of the Estimating Spreadsheet

    • Enter your own name in the CustomerDB tab.
    • Create a formula that will combine first and last names together.
    • Apply special formatting for the Zip Code column.
    • Apply special formatting for the Phone Number column.
    • Create a custom named range for the Customer List
    • Create a custom named range for the Customer Database.

    Summary Tab of the Estimating Spreadsheet

    Complete the calculations for each of the following figures (displayed in orange):

    • Insert data validation into the Customer Name field to create a dropdown from the Customer List range.
    • Create VLOOKUP formulas to populate customer data from the Customer Database range in the appropriate fields for the following:
      • Street Address
      • City
      • State
      • Zip Code
      • Phone Numbers (Home, Work, and Cell)
      • Lot#
      • Community

    Walkthrough Videos

    CustomerDB Tab Walkthrough, 19:48 mins

    Format the CustomerDB Tab of the Estimating Spreadsheet

    The first step is to enter some information in the CustomerDB tab and format specific cells for the type of information they contain.

    Step 01: Enter Names and Contact Information for Yourself

    1. Open the Estimating Worksheet and navigate to the CustomerDB tab.
    2. Enter your own last and first names and other contact information in Row 3 of the CustomerDB tab of the Estimating Worksheet. Do NOT enter anything into the “Name” column yet.
    3. Sample information has already been input for three other customers (Figure 5-6).
    Figure 5-6 select the CustomerDB tab and enter your contact information as the first customer.
    Figure 5-6 select the CustomerDB tab and enter your contact information as the first customer.

    Step 02: Enter a Formula to Combine the First and Last Names Together

    1. Go to Cell A3 of the CustomerDB tab.
    2. Enter the following formula to combine the information in the “Last Name” and “First Name” columns in the “Name” column.
      • =C3 &” “&B3
      • This formula will simply pull the contents B3 and C3 and display them in the order you choose (Figure 5-7).
    = c3&" "&b3 be sure to add ampersands, quotes, and a space
    Function to correctly order last and first names
    Figure 5-7 shows the formula written in Excel.
    Figure 5-7 shows the formula written in Excel.
    1. Complete the same formula in the other cells in Column A

    Step 03: Format the Zip Code Column

    1. Select the cells in the Zip Code column.
    2. Right-click with the cells highlighted, then select the Format Cells option
    3. Select Number → Special → Zip Code (Figure 5-8).
    Figure 5-8 Format Zip Code
    Figure 5-8 Format Zip Code

    Step 04: Format the Phone Number Column

    1. Select the cells in the Phone Number column.
    2. Right-click with the cells highlighted, then select the Format Cells option
    3. Select Number → Special → Phone Number (Figure 5-9).
    Figure 5-8 Format cells to display phone numbers
    Figure 5-8 Format cells to display phone numbers

    Create a Customer List Named Range and a Customer Database Named Range

    The next step in setting up the customer database is to create a list of the customer names. This will be used to allow the use of drop-downs to select customers in the other tabs of the worksheet.

    Step 01: Create a Customer List Named Range

    1. Select the cells in column A from A3 to A16.
    2. Click the Name Box to the left of the function input field, and type CustomerList (Figure 5-10). Save by pressing Enter.
    Figure 5-10 Name cells A3 to A16 CustomerList
    Figure 5-10 Name cells A3 to A16 CustomerList

    Step 02: Create a Customer Database Named Range

    1. Select the range of cells from A3 to K16.
    2. Click the Name Box to the left of the function input field, and type CustomerDB (Figure 5-12). Save by pressing Enter.
    Figure 5-12 Create a group of named cells called CustomerDB
    Figure 5-12 Create a group of named cells called CustomerDB

    Use Data Validation to Create a Drop-Down Selection List

    The named range titled CustomerList can be used with the Excel Data Validation command to create a custom drop-down selection list that can be placed in a cell, or group of cells. Data Validation will limit the data that can be placed in the cell to only data specifically upon the list. In this case, it will limit the data in a cell to a customer’s name that is included in the CustomerList.

    Step 01: Insert Data Validation into the Worksheet

    1. Navigate to the Summary tab of the Estimating Worksheet.
    2. Select the blank cell directly under the heading Customer Name.
    3. Navigate to Data → Data Tools → Data Validation on the Ribbon (Figure 5-14).
    Figure 5-14 Creating a custom drop-down menu by using Data Validation
    Figure 5-14 Creating a custom drop-down menu by using Data Validation
    1. Click on the drop-down arrow in the input box directly underneath the Allow: parameter and change the value from Any value to List by selecting it from the menu (Figure 5-15).
    Figure 5-15 Choosing a list in Data Validation
    Figure 5-15 Choosing a list in Data Validation
    1. Click in the empty box and press the F3 key on the keyboard. The F3 key is an Excel shortcut key to display the Paste Names dialog box.
    2. Scroll down in the list of names until CustomerList is visible and click on the name to highlight it. Press Ok to paste the name in the Source input box (Figure 5-16).
    Figure 5-16 Choosing CustomerList as the Source for validation
    Figure 5-16 Choosing CustomerList as the Source for validation
    1. The Source: value should read =CustomerList. Press OK to clear the Data Validation dialog box and return to the spreadsheet.
    2. Click on the drop-down arrow that displays on the right side of cell C3. If the names from the CustomerDB tab are selectable in the drop-down, you have successfully completed this task.

    Create Formulas to Input Customer Data

    The next step will be to write Excel functions to automatically place the customer’s information in the header of the summary sheet when a customer’s name is selected from the drop-down list. The Excel VLOOKUP function will be used to do this.

    Step 01: Identify the Destination Cells in the Summary Tab.

    1. The destination cells for customer data are the empty cells in the header of the Summary tab. They include the following:
      • Street Address
      • City
      • State
      • Zip Code
      • Phone Numbers (Home, Work, and Cell)
      • Lot #
      • Community

    Step 02: Enter a VLOOKUP Function in a Destination Cell.

    1. Select the destination cell (eg. Street Address).
    2. Click the small Insert Function icon located to the left of the formula bar.
    3. Change the category from Most Recently Used to All. Scroll down the list of available function until VLOOKUP is found. Highlight the function and click OK (Figure 5-19).
    Figure 5-19 Insert VLOOKUP Function in Street Address Cell
    Figure 5-19 Insert VLOOKUP Function in Street Address Cell
    1. Select the Lookup_value cell. This is the value you want to look up in the database. In this case, it is the name of the customer, which can be selected in the drop-down you created in C3. Therefore, the Lookup-value is C3 (Figure 5-20).
    Figure 5-20 the Lookup_vlaue in the Function Arguments dialog box
    Figure 5-20 the Lookup_vlaue in the Function Arguments dialog box
    1. The Table_array argument will be filled in next. This argument is for the database where the needed content is; in the case, the CustomerDB. The easiest way to do this is to click in the Table_array box and again use the F3 Function Key on the keyboard to bring up the Paste Names dialog box. Find the CustomerDB from the list and highlight it and select OK (Figure 5-21).
    Figure 5-21 The Table_array in the Function Arguments dialog box
    Figure 5-21 The Table_array in the Function Arguments dialog box
    1. The third argument is the Col_index_num. This is the column number of the cell where the content is located; in this case, the Address is located in Column D, which is the fourth column of the CustomerDB (see Figure 5-18). Enter a 4 into the Col_index_num of the dialog box.
    2. The fourth argument is the Range_lookup argument. This argument has two possible inputs, either True or False. Inputting True means that Excel will look for the closest match. Inputting False means that Excel will need to find an exact match.False is the correct answer for most uses in this spreadsheet. Click OK to finish entering the function in the cell (Figure 5-22).
    Figure 5-22 The Range_lookup in the Function Arguments dialog box with the correct preview
    Figure 5-22 The Range_lookup in the Function Arguments dialog box with the correct preview
    1. Check whether the VLOOKUP works by selecting a different customer from the drop-down in C3.
      • If your function is not working correctly, check the syntax by double-clicking the cell with the VLOOKUP. It should read as =VLOOKUP(C3,CustomerDB,4,FALSE).
    1. Complete a similar VLOOKUP function for the other destination cells in the header of the Summary tab.

    This article contains original content by BYU-Idaho. It is compiled and licensed under a CC BY-SA 4.0 license.

    Previous Citation(s)
    (n.d.). Construction Estimating Quick Guides and Walkthroughs. BYU-I Books. https://books.byui.edu/-JxEq

    This content is provided to you freely by BYU-I Books.

    Access it online or download it at https://books.byui.edu/construction_estimat/customer_database_wa.