Worksheet Tutorial: Creating a Wall Schedule

This simple tutorial explains how to create a customized schedule that links wall objects in a drawing to a database worksheet. The schedule will display data for wall areas, wall thicknesses, labor cost, material cost, taxes, and total cost per wall style.

The WorksheetTutorial.vwx file contains the data required to perform the tutorial steps. Though wall styles are a Vectorworks Design Series feature, any Vectorworks license can use the wall styles that already exist in the tutorial file. Download the file here (internet access required) and open it in Vectorworks to begin.

Step 1: Create a Blank Worksheet

There are three ways to create a schedule using worksheets. This tutorial uses the first option below.

        Start with a blank worksheet, and create the schedule from scratch. See Creating a Blank Worksheet.

        Create a schedule using the Create Report dialog box, where you can specify the criteria for the database rows and the columns of data to display. See Creating Reports.

        Start with a preformatted report and customize it to achieve your goals. See Using Preformatted Reports.

To create a blank worksheet:

  1. From the Resource Manager, click New Resource, select Worksheet, and then click Create. The Create Worksheet dialog box opens.

  2. Enter “Wall Area and Costs” as the name for the new worksheet. You will add more rows and columns later.

A blank worksheet window opens.

ws_tutorial1.png 

Step 2: Set the Database Criteria

Next, create a database of the objects in the drawing from which to extract the wall area data. You can combine multiple criteria to collect the desired subset of objects.

For this tutorial, a single database of wall objects will be created and limited to a specified set of wall styles.

An alternative would be to create one database per wall style and include multiple databases in the same worksheet. However, for very large databases, it is recommended to create separate worksheets rather than include multiple databases into a single worksheet.

To set the database criteria:

  1. Right-click on the header box for row 3, and select Database from the context menu. The Criteria dialog box opens.

  2. Set the three criteria options as follows:

  3.         Type

            is

            Wall

  4. Click More Choices, and set the next three criteria as follows:

  5.         Wall Style

            is

            Aspen Interior Walls

  6. Click More Choices, and set the next three criteria as follows:

  7.         Wall Style

            is

            Ext-CIP Conc 36”-Footing

    To include all wall styles in the schedule, do not enter criteria for walls and wall styles; instead, use the following criteria: Record, Wall Data, is present.

    ws_tutorial2.png 

  8. Click OK to set the criteria. The database of walls for the specified set of wall styles is created. The database header (row 3) now has a diamond next to the row number. Beneath row 3 are sub-rows for each object in the database (3.1 through 3.45).

ws_tutorial3.png 

Step 3: Expand the Worksheet

For this tutorial, you need to expand the worksheet. Since no data has been assigned to the columns yet, it does not matter where the columns are added.

Use one of the following methods to add three columns to the worksheet, for a total of eight.

        Select Insert > Columns. An empty column is added to the left of the current column.

        Right-click the column header where you want to add a column, and select Insert Columns from the context menu.

        Position the cursor at the bottom right corner of the worksheet to activate a special resize cursor; drag to add columns to the right side of the worksheet.

Step 4: Use Worksheet Functions to Extract Data

Next, add database functions to the worksheet to extract the desired data from the database. Select a function for each cell in the database header row.

For this tutorial, the following data will be extracted:

        Wall Style Name

        Gross Wall Area

        Net Wall Area

        Wall Thickness

To extract the data associated with the walls in the database:

  1. From each of the following cells, click the button on the right side of the cell, select Functions, and then select the function shown to extract data for each item in the database.

  2.         In A3 select WallStyleName

            In B3 select WallArea_Gross

            In C3 select WallArea_Net

            In D3 select WallThickness

    Alternatively, use the worksheet menu command Insert > Function to insert functions.

  3. By default, numerical data is unformatted and must be formatted to display appropriate units. Formatting applied to a database header cell is automatically applied to all sub-rows for that column.

  4. Right-click each of the following cells, and select Format Cells from the context menu. The Format Cells dialog box opens. On the Number tab, select the format option shown.

            For B3 select Dimension Area

            For C3 select Dimension Area

            For D3 select Dimension

    Alternatively, use the worksheet menu command Format > Cells to format cells.

  5. Add labels for columns A through D by typing names in the cells in row 2.

        In A2 enter Wall Style Name 

        In B2 enter Wall Area (Gross) 

        In C2 enter Wall Area (Net) 

        In D2 enter Wall Thickness 

ws_tutorial4.png 

Various types of data can be extracted from Vectorworks objects into a worksheet database, as described in the following topics.

        Worksheet Functions

        Displaying Object Properties in a Database Column

        Displaying Record Information in a Database Column

        Entering Formulas in Worksheet Cells

Step 5: Summarize Wall Styles

Instead of listing each wall individually in the database, you can summarize all walls with the same wall style, automatically calculating the total quantities for each, and shortening the list.

To summarize the wall styles:

  1. Click the button in A3, which is the database header cell for wall styles.

  2. From the dialog box that opens, click Summarize Items. The number of sub-rows is reduced to only two (one row per wall style).

  3. The numerical values in columns B and C are now dashes, because the area is different for each wall. To show the area values for all walls combined, click the button in B3, click Sum Values. Repeat for cell C3.

ws_tutorial5.png 

Step 6: Use Formulas to Calculate Costs

Next, calculate costs using worksheet operations and formulas.

For this tutorial, the following data will be calculated:

        Labor cost per wall style

        Material cost per wall style

        Taxes

        Total cost

To calculate costs with formulas:

  1. Add labels for columns E through H by typing names in the cells in row 2.

  2.         In E2 enter Labor Cost 

            In F2 enter Material Cost 

            In G2 enter Taxes 

            In H2 enter Total Cost 

  3. Click the following cells and enter the formula shown to determine the cost sums. Be sure to include the equal sign (=) before each item. When each formula is complete, click the green check mark or press Enter to validate the entry.

  4.         In E3 enter =C3*15.5 (multiply the value in C3 by 15.5, the estimated rate of labor cost per area unit)

            In F3 enter =C3*9.2 (multiply the value in C3 by 9.2, the estimated material cost per area unit)

            In G3 enter =F3*.08 (multiply the value in F3 by 0.08, the estimated tax rate)

            In H3 enter =E3+F3+G3 (calculate the total cost of labor, material, and taxes)

    Instead of entering the tax rate directly into the calculation, an alternative and more flexible approach is to set the tax rate in a separate spreadsheet cell and simply reference it in the calculation.

  5. The numerical values in columns E through H are now dashes, because the cost is different for each wall. To show the cost values for all walls combined, click the button in E3, click Sum Values. Repeat for cells F3, G3, and H3.

  6. Format all of the cost data the same way. Select the four cost header cells (E3 through H3), and then right-click and select Format Cells from the context menu. The Format Cells dialog box opens. On the Number tab, select the options shown.

        Select Decimal

        In Dec. Places, enter 2

        Select Use Commas

        In Leader, enter $ (dollar sign)

ws_tutorial6.png 

Step 7: Set up Column Totals, and Add and Delete Rows

Next, set up totals at the bottom of the columns as appropriate. The cells in the database header row (in this case, row 3) display sums for all of the database columns. Reference these database header row cells in spreadsheet cells to set up the totals.

To set up the totals:

  1. Click the following cells and enter the formula shown to show sums from the database header row. Be sure to include the equal sign (=) before each item.

  2.         In B4 enter =B3 

            In C4 enter =C3 

            In E4 enter =E3 

            In F4 enter =F3 

            In G4 enter =G3 

            In H4 enter =H3 

  3. Select cells B4 and C4, right-click, and select Format Cells from the context menu. On the Number tab, select Dimension Area.

  4. Select cells E4 through H4, right-click, and select Format Cells from the context menu. On the Number tab, select the options shown.

  5.         Select Decimal

            In Dec. Places, enter 2

            Select Use Commas

            In Leader, enter $ (dollar sign)

  6. Click cell A1 and enter Wall Areas and Costs as the schedule title.

  7. Select row 2, right-click, and select Insert Rows from the context menu to add an empty row between the schedule title and the column labels.

  8. Select the empty rows at the bottom of the worksheet (6 through 11), right-click, and select Delete Rows from the context menu.

  9. Select View > Grid Lines to hide the grid lines in the table.

ws_tutorial7.png 

Step 8: Format the Worksheet

Finally, select cells and use the Format Cells command to format the worksheet. You can change the font, font style, size, and color to format the text. Add cell borders, and change cell background color, as desired. Change the text alignment in cells, and resize rows and columns, if necessary. Select File > Worksheet On Drawing to place the worksheet in the drawing area.

See Formatting Worksheet Cells for details.

ws_tutorial8.png 

ws_tutorial9.png 

 

 

 

Was this page helpful?