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.
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.
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:
Right-click on the header box for row 3, and select Database from the context menu. The Criteria dialog box opens.
Set the three criteria options as follows:
● Type
● is
● Wall
Click More Choices, and set the next three criteria as follows:
● Wall Style
● is
● Aspen Interior Walls
Click More Choices, and set the next three criteria as follows:
● 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.
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).
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.
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, 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.
● Displaying Object Properties in a Database Column
● Displaying Record Information in a Database Column
● Entering Formulas in Worksheet Cells
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.
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:
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)
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.
Select View > Grid Lines to hide the grid lines in the table.
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.
