Tutorial: Creating a wall schedule worksheet
Difficulty level: Intermediate
This simple worksheet 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, supply/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 Suite feature, any Vectorworks license can use the wall styles that already exist in the tutorial file.
Download the file and open it in Vectorworks to begin (internet access required): WorksheetTutorial.vwx
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:
From the Resource Manager, click New Resource, select Worksheet, and then click Create. The Create Worksheet dialog box opens.
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.
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 1 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:
Right-click on the header box for row 3, and select Database from the context menu. The Criteria dialog box opens.
In row 1, the primary condition is already set to "All criteria in this set."
In row 2, click in each column to create the following criteria statement:
Type
is
Wall
If you wanted to include all wall styles in the schedule, you would stop here and not enter criteria for wall styles. For this tutorial, you need to include two specific wall styles and exclude all others, which will require more criteria.
Click Add, and then select "Any criteria in this set" in the Criteria column in row 3 to create a sub-condition set.
In row 4, create the following criteria statement:
Wall Style
is
Aspen Interior Walls
Click Add, and then create the following criteria statement in row 5:
Wall Style
is
Ext-CIP Conc 36"-Footing
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.
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 doesn't matter where the columns are added. Use one of the following methods to add three columns to the worksheet, for a total of eight.
Click any cell and then select Insert > Columns. A column is added to the left of the current column.
Right-click any cell and select Insert Columns from the context menu. A column is added to the left of the current column.
Position the cursor at the bottom right corner of the worksheet to activate a special resize cursor; drag to the right 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:
From each of the following cells, click the button on the right side of the cell, select Functions, then Specialized for Wall, and then select the function shown to extract data for each item in the database.
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.
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.
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.
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
Various types of data can be extracted from Vectorworks objects into a worksheet database, as described in the following topics.
Selecting a function or field for 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:
Click the button in A3, which is the database header cell for wall styles.
From the dialog box that opens, click Summarize Items. The number of sub-rows is reduced to only two (one row per wall style).
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.
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
Supply/material cost per wall style
Taxes
Total cost
To calculate costs with formulas:
Add labels for columns E through H by typing names in the cells in row 2.
In E2 enter Labor Cost
In F2 enter Material Cost
In G2 enter Taxes
In H2 enter Total Cost
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.
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.
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.
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)
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:
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.
In B4 enter =B3
In C4 enter =C3
In E4 enter =E3
In F4 enter =F3
In G4 enter =G3
In H4 enter =H3
Select cells B4 and C4, right-click, and select Format Cells from the context menu. On the Number tab, select Dimension Area.
Select cells E4 through H4, right-click, and select Format Cells from the context menu. On the Number tab, select the options shown.
Select Decimal
In Dec. Places, enter 2
Select Use Commas
In Leader, enter $ (dollar sign)
Click cell A1 and enter Wall Areas and Costs as the schedule title.
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.
Select the empty rows at the bottom of the worksheet (6 through 11), right-click, and select Delete Rows from the context menu.
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.
Formatted worksheet placed in drawing