Worksheets

The worksheet functionality in the Vectorworks program complements its drawing functionality, making it a complete package for your work process. From the information present in the file, you can create worksheets to track data, create cost and material lists, perform calculations, and more. Worksheets are integrated within the Vectorworks file, which eliminates the need for a separate program and reduces the number of files per project.

Worksheets include both database and spreadsheet functionality. Data can be obtained from the drawing, and then calculations can be performed on that data.

Worksheets can be imported and exported, which allows data to be shared between worksheets, files, and other spreadsheet programs. A worksheet can also be added to a drawing and printed.

To get a quick overview of worksheet features, see “Worksheet Tutorial: Creating a Wall Schedule” on page 1357.

~~~~~~~~~~~~~~~~~~~~~~~~~

Creating Worksheets

Creating Schedules Automatically

Using Worksheets

Selecting and Editing Worksheet Cells

Worksheet Commands

Database Row Sort and Summary Functions

Formatting Worksheet Cells

Entering Data in Spreadsheet Cells

Entering Data in Database Rows

Inserting Images in Worksheets

Worksheet Functions

Importing Worksheets

Exporting Worksheets

Worksheets as Graphic Objects

Worksheet Tutorial: Creating a Wall Schedule

Creating Worksheets

For complex drawings, it is best to create separate worksheets for each task rather than one large worksheet. Worksheets can be linked to share data, formulas, and calculations.

Worksheets can be created in several ways:

•  Use the Create Report command to select worksheet data from the information associated with the objects in the drawing. See “Creating Reports” on page 1312.

•  Use the Resource Browser to create a blank worksheet, and then add the desired information to it. See “Creating a Blank Worksheet” on page 1313.

•  Import worksheets from other Vectorworks files or from other spreadsheet programs. See “Importing Worksheets” on page 1354.

•  If Vectorworks Design Series is installed, preformatted records and schedules such as room finishes, plant lists, and lighting instruments can be added to the drawing. See “Creating Schedules” on page 1858 and “Creating Schedules Automatically” on page 1314.

Once created, a worksheet is saved with the file and is listed in the Resource Browser. It can also be accessed by selecting Window > Worksheets.

If the same objects are typically used in your drawings, you can create a template file with a worksheet that serves as a “master price list” listing all the objects and their costs. Then, to create materials lists and cost estimates for a new design, simply import the worksheet into the new drawing file.

Creating Reports

The Create Report command allows you to select data that is attached to objects in a drawing (such as manufacturer, size, and price) and create a worksheet from it. The command can either create a new worksheet or append database rows to an existing worksheet. For more information about how to attach data to objects, see “Registros” on page 257.

To create a report from objects in a drawing:

1. Select the Create Report command from the appropriate menu:

•       Fundamentals, Architect, Landmark: Tools > Reports > Create Report

•       Spotlight: Spotlight > Reports > Create Report

The Create Report dialog box opens. Specify the report criteria. Items in the Worksheet Columns list are listed in the order in which they will appear in the worksheet; to change the order, click in the # column and drag the item to the desired position in the list.

Click to show/hide the parameters.

2. Click Options to specify additional report criteria.

The Create Report Options dialog box opens.

Click to show/hide the parameters.

3. Specify the options for creating the report, and click OK to return to the Create Report dialog box.

4. Click OK to create the worksheet.

The worksheet opens automatically. The top row of the worksheet contains a title for each column selected. Next is a database header row (indicated by a diamond next to the row number) that contains sub-row totals for each column. Beneath the header row are sub-rows for each object or symbol in the drawing that matches the report criteria.

5. To add more data to the worksheet, repeat steps 1 through 4 and select the Append to existing worksheet option.

6. Once all the data is added, edit the worksheet as needed. For example, add rows or columns, change the text format, or add color. To hide the database header rows, toggle the Database Headers setting on the Worksheet menu.

Click here for a video tip on this topic (Internet connection required).

~~~~~~~~~~~~~~~~~~~~~~~~~

Creating a Blank Worksheet

Using Worksheets

Importing Worksheets

Creating a Blank Worksheet

Instead of using the Create Report command, you can create a blank worksheet and add data to it manually. This gives you more control over the contents and format of the worksheet.

To create a blank worksheet:

1. Select Window > Palettes > Resource Browser.

2. Select Resources > New Resource to display the list of new resource types.

3. Select Worksheet.

The Create Worksheet dialog box opens.

Click to show/hide the parameters.

4. Specify the basic worksheet parameters and click OK.

A new worksheet window opens.

5. At this point, all rows contain spreadsheet cells, and they are all undefined. Define the contents of each row and cell as needed:

•       To add simple text, numbers, or formulas to the worksheet, see “Entering Data in Spreadsheet Cells” on page 1329.

•       To list data that is associated with objects in the drawing, change a spreadsheet row into a database row, and specify which objects to include in the list. A sub-row displays for each object that matches the criteria defined in the database header row. Then specify which information to display in the columns for each row; these can be fields from the object’s data record, as well as text, numbers, or formulas. See “Entering Data in Database Rows” on page 1335.

•       To add images to either spreadsheet or database rows, see “Inserting Images in Worksheets” on page 1339 (Vectorworks Design Series required).

~~~~~~~~~~~~~~~~~~~~~~~~~

Creating Reports

Using Worksheets

Creating Schedules Automatically

The Vectorworks Landmark and Spotlight products include a Choose Schedule command with preformatted schedules that are generated automatically. The Vectorworks Architect product includes preformatted, customizable records and schedules as described in “Records and Schedules” on page 1853. The schedules can be created any time; as objects are added to the drawing, recalculate the worksheet to update the results.

The preformatted schedules are from the default content included with the Vectorworks Design Series products in the [Vectorworks]\ Libraries\Defaults\Reports~Schedules folder (see “Resource Libraries” on page 215).

The default schedules for Vectorworks Landmark include plant lists, irrigation plans, and existing tree reports, with or without images. Plants to be included in the Plant List worksheets must have On Plant List selected in the Plant Settings dialog box or the Object Info palette.

A formatted worksheet can be saved as a custom schedule to be used again; export the worksheet resource (renamed with a custom name) to a file in the Reports~Schedules folder in your user folder. Your customized worksheet becomes available for selection in the Choose Schedule dialog box. For information about exporting resources, see “Exporting Custom Resources” on page 230.

Another way to create a schedule is to select the Create Report command. List the objects with the relevant record, such as the Existing Tree record, and then select the desired columns to include in the report. See “Creating Reports” on page 1312 for more information.

To add a schedule:

1. Select a command from the appropriate menu:

•       Landmark: Tools > Reports > Choose Schedule

•       Spotlight: Spotlight > Reports > Choose Schedule

•       Architect: Tools > Reports > VA Create Schedule

The Choose Schedule dialog box (for Landmark and Spotlight) or Create Schedule dialog box (for Architect) opens. Select one of the worksheets to create; select Place worksheet on drawing to add the worksheet to the drawing area.

2. Click OK to create the selected worksheet.

If the selected schedule already exists in the file, a warning dialog box opens. Select whether to replace or rename the new schedule (some schedules also have a recalculate option), and click OK.

3. If the worksheet is to be placed on the drawing, click to indicate the position of the top left corner of the worksheet.

The worksheet, populated with specific information from the current drawing, is automatically created.

~~~~~~~~~~~~~~~~~~~~~~~~~

Worksheets

Resource Libraries

Adding Plants to the Design

Placing Existing Trees

Irrigation

Records and Schedules

Using Worksheets

Worksheets can obtain data from the drawing based on specified criteria, and then list the data and allow calculations to be performed on the data.

Worksheets can have two types of rows: spreadsheet and database. The cells in a spreadsheet row contain constants (text or numbers), or formulas. Database rows consist of a header row and sub-rows, and they show data that are associated with specific drawing objects. The database header row is marked with a diamond shape next to the row number. Set selection criteria for this row, and a sub-row is created for each object that meets the criteria. In this example, the database header row 2 is set to list each object in the drawing that has an “appliance record” attached to it. The sub-rows 2.1 through 2.5 represent the five objects in the drawing that meet this criteria.

Rows are numbered sequentially starting with 1, and columns are labeled alphabetically starting with A. Database sub-rows are numbered with the database header row’s number, followed by a decimal and sequential numbers (header row 2 has sub-rows 2.1, 2.2, 2.3, and so on). The cell’s column letter and row number indicate the spreadsheet cell address, as in A4 or D2 (database sub-rows display a blank address).

When worksheets exist in an open file, the Window > Worksheets command becomes available. All the worksheets present in the indicated file are listed. Worksheets with a check mark are currently open. To open a worksheet, select it from this menu, or select the worksheet from the Resource Browser and then select Resources > Open.

A worksheet opens in a separate window, which can be resized, moved, and closed. The worksheet window contains its own menu and context menus.

Because an open worksheet is in a separate window, it is not printed with the drawing. To include a worksheet as part of a drawing, select the worksheet in the Resource Browser and click Resources > Worksheet on Drawing. When the worksheet is open, the worksheet on the drawing displays as an “X.” When the worksheet is closed, the updated worksheet displays on the drawing. Double-click the worksheet from the drawing to open it. See “Worksheets as Graphic Objects” on page 1356.

Use the Format Cells command to format individual rows, columns, and even cells of the worksheet as needed. The format is retained when the worksheet is included on the drawing. Alternatively, with the worksheet window closed, select the worksheet object on the drawing, and use the Attributes palette to modify the fill, pen, and line thickness attributes for the entire object.

~~~~~~~~~~~~~~~~~~~~~~~~~

Selecting and Editing Worksheet Cells

Worksheet Commands

Formatting Worksheet Cells

Selecting and Editing Worksheet Cells

The contents of cells can be edited, and the rows and columns can be resized, moved, cut, copied, and pasted.

Moving Around in Worksheets

The following table describes the keys used to move around in the worksheet.

If more than one cell is selected, movement is restricted to the selected cells only.

Selecting Cells

Selected cells are surrounded with a black outline. When multiple cells are selected, the active cell is white, and the remaining selected cells are highlighted in blue.

Editing Cell Contents

When a cell is selected, the display of the Formula bar indicates whether the contents of the cell can be edited.

Resizing Rows and Columns

To adjust column width or row height, drag the divider bar between the column letters or row numbers. If you select multiple rows or columns before you drag the bar, all rows or columns are adjusted to the same size.

Alternatively, select the Column Width command from either the Format menu or the column context menu (see “Column Width” on page 1322). Adjust the row height with the Row Height command from either the Format menu or the row context menu (see “Row Height” on page 1322).

Text that is longer than the width of a cell “floats” over empty adjacent cells. Numbers that exceed the cell width are displayed with # characters. Alternatively, text can be set to wrap (see “Formatting Worksheet Cells” on page 1326).

To hide a row or column, set the Column Width or Row Height to 0. To display the column or row again, position the cursor over the column letter or row number adjacent to the hidden column or row. When the cursor changes to a double bar resize cursor, click-drag the bar to resize the column or row.

Cutting, Copying, and Pasting Cell Contents

The standard shortcut keys for Cut and Paste can be used for worksheet editing. The same value or formula can be copied and pasted to a range of cells.

To copy cell contents to a cell or range of cells:

1. Select the cell with the information to repeat, and then press Ctrl+C (Windows) or Command+C (Mac) to copy the cell.

Alternatively, select the Copy command from either the Edit menu or the appropriate context menu (see “Copy” on page 1321).

2. Select the destination cell(s) for the information, and then press Ctrl+V (Windows) or Command+V (Mac) to paste. The formula or value is repeated in each of the selected cells.

Alternatively, select the Paste command from either the Edit menu or the appropriate context menu (see “Paste” on page 1321).

Adding Rows and Columns

From the Insert menu, you can insert rows and columns (see “Worksheet Commands” on page 1320). An empty row is added above the current row, or an empty column is added to the left of the current column. Another option is to position the cursor at the bottom right corner of the worksheet to activate a special resize cursor; drag as needed to add rows and columns to the bottom and right side of the worksheet.

Moving Rows and Columns

Use the drag and drop method to move contiguous rows and columns or to move a copy of contiguous rows and columns.

To move a copy of rows or columns:

1. Click the column letter or row number to select a column or row (click-drag across the letters or numbers to select multiple columns or rows).

2. Press and hold Ctrl (Windows) or Option (Mac) and move the cursor to the edge of the selected rows or columns. When the cursor changes to a copy cursor to indicate that moving a copy is permitted, drag the selection to the desired location in the worksheet.

To move rows or columns:

1. Click the column letter or row number to select a column or row (click-drag across the letters or numbers to select multiple columns or rows).

2. Move the cursor to the edge of the selected rows or columns. When the cursor changes to a move cursor to indicate that moving the rows or columns is permitted, drag the selection to the desired location in the worksheet.

~~~~~~~~~~~~~~~~~~~~~~~~~

Editing Record Information with Worksheets

Worksheet Commands

Various command menus are available in the worksheet window, as well as sorting functions. The main worksheet menu is at the top of the window. Right-click (Windows) or Ctrl-click (Mac) on a specific worksheet row or cell to open a context menu. To sort the sub-rows associated with a database header row, click and drag a sort icon to the column header cell.

~~~~~~~~~~~~~~~~~~~~~~~~~

Worksheet Menu

Worksheet Cell Context Menu

Worksheet Row Context Menu

Worksheet Column Context Menu

Worksheet Menu

The main Worksheet menu commands are described in the following table.

~~~~~~~~~~~~~~~~~~~~~~~~~

Worksheet Cell Context Menu

Worksheet Row Context Menu

Worksheet Column Context Menu

Worksheet Cell Context Menu

To access the commands available for a specific worksheet cell, right-click (Windows) or Ctrl-click (Mac) on the cell.

~~~~~~~~~~~~~~~~~~~~~~~~~

Worksheet Menu

Worksheet Row Context Menu

Worksheet Column Context Menu

Worksheet Row Context Menu

To access the commands available for a specific worksheet spreadsheet or database header row, right-click (Windows) or Ctrl-click (Mac) while on the row number. These commands do not apply to database sub-rows.

Database Sub-Row Context Menu

While on a database sub-row, right-click (Windows) or Ctrl-click (Mac) and select the Select Item command from the context menu. Use this command to select an individual database object in the drawing; the view changes to display the selected object (see “Importing Worksheets” on page 1354). The command is unavailable if the sub-row is summarized (see “Database Row Sort and Summary Functions” on page 1325).

~~~~~~~~~~~~~~~~~~~~~~~~~

Worksheet Menu

Worksheet Cell Context Menu

Worksheet Column Context Menu

Worksheet Column Context Menu

To access the commands available for a specific worksheet column, right-click (Windows) or Ctrl-click (Mac) while on the column letter.

~~~~~~~~~~~~~~~~~~~~~~~~~

Worksheet Menu

Worksheet Cell Context Menu

Worksheet Row Context Menu

Database Row Sort and Summary Functions

The group of sub-rows associated with a header row can be sorted and summarized in different ways. These functions can be set independently for each database header row in a worksheet.

The sort and summary functions cannot be used on a header cell for an image column (Vectorworks Design Series required).

To sort or summarize a group of database sub-rows:

1. If the database header rows are not displayed, select View > Database Headers from the Worksheet menu.

2. Select the header row of the group of sub-rows to sort or summarize; the header row has a diamond next to its number.

The three icons above the left end of the column header cells become available.

3. Click and drag an icon to the column header cell to be used for the sort or summary. A new icon displays next to the column heading letter. For an ascending or descending sort, a number in the icon indicates the sort precedence for that column.

4. Apply additional sort or summary icons as needed. In each group of sub-rows, up to 20 columns can have either an Ascending or Descending Sort icon, and any number of columns can have a Summarize icon. The Summarize icon can be used on a column by itself, or in conjunction with one of the sort icons.

5. To remove a sort or summary, click and drag the icon away from the column header cell.

Formatting Worksheet Cells

The appearance of worksheet cells can be set by a variety of formatting options.

Formatting applied to a database header row applies to all of the associated database sub-rows.

To format worksheet cells:

1. Select the cell(s) to format.

2. From the Worksheet menu, select Format > Cells.

The Format Cells dialog box opens.

On the Number tab, set the number format for the selected cells.

Click to show/hide the parameters.

3. Click the Alignment tab to specify text alignment options.

Click to show/hide the parameters.

4. Click the Font tab to specify the font, font size, style, and color of text in selected cells. See “Formatting Text” on page 385.

5. Click the Border tab to set cell border formatting options.

Select the Line Attributes, and then use the Presets or Preview buttons to add or remove border elements.

Click to show/hide the parameters.

6. Click the Patterns tab to specify fill options for the selected cell(s).

Click to show/hide the parameters.

7. If Vectorworks Design Series is installed, click the Images tab to specify the type, size, view, and margin for images in the selected cells. For more information, see “Inserting Images in Worksheets” on page 1339.

Click to show/hide the parameters.

8. Click OK to set the formatting for the selected cell(s). The worksheet formatting also applies to worksheets placed on a drawing.

Click here for a video tip on this topic (Internet connection required).

~~~~~~~~~~~~~~~~~~~~~~~~~

The Attributes Palette

Entering Data in Spreadsheet Cells

Three types of information can be entered into the spreadsheet cells of a worksheet: constant values (including text or numbers), formulas, and images (Vectorworks Design Series required). In addition, a cell can reference another cell in the same worksheet or in another worksheet.

•  Text helps to identify the purpose of a worksheet and labels the columns in a worksheet.

•  Images add visual information about items on a worksheet, and can also be used to create a drawing legend (Vectorworks Design Series required).

•  Use formulas to perform calculations based on drawing data. A formula can be a simple mathematical equation, or it can include one or more built-in functions. The Vectorworks program provides mathematical functions (for example, a square root function), as well as functions that pull information from drawing objects (for example, a function that returns the volume of selected objects). See “Worksheet Functions” on page 1340 for a list of the functions available.

Database record fields that are attached to objects in the drawing (such as Model Number or Price) cannot be used in a spreadsheet cell. To include this type of data in the worksheet, see “Entering Data in Database Rows” on page 1335.

To define a spreadsheet row:

1. Right-click (Windows) or Ctrl-click (Mac) on the number of the row to change.

2. From the Row context menu, select Spreadsheet.

3. The cells in the row are empty until you define the contents. Select a cell, and then enter the desired information in the worksheet Formula bar located at the top of the worksheet.

•       To enter text or numbers, see “Entering Constant Values in Worksheet Cells” on page 1330.

•       To enter a formula, see “Entering Formulas in Worksheet Cells” on page 1331.

•       To reference other cells in this cell, see “Referencing Other Worksheet Cells” on page 1334.

•       To insert an image, see “Inserting Images in Worksheets” on page 1339.

Entering Constant Values in Worksheet Cells

Constant values consist of numbers, spaces, non-numeric characters, or any combination of these. Constant values are not part of a formula or the result of a formula.

The formula phrase “=1”, or any number following an equal sign, is also considered a constant value.

Select the cell, and then enter the text or numbers needed; your entries automatically display in the worksheet Formula bar. When you click the green check mark on the Formula bar, the value displays in the cell.

Keep in mind the following:

•  Most constant values are treated as text and default to the General format. However, some combinations of numeric and non-numeric characters are interpreted as a particular number format. For example, an entry of 07/19/2013 automatically changes the format to the month/day/year date format. (See “Formatting Worksheet Cells” on page 1326.)

•  Text is left-aligned unless the cell is formatted differently on the Alignment tab in the Format Cells dialog box (select Format > Cells from the Worksheet menu).

•  Numbers entered in cells default to the General format. To change them to another format (for example, dimension or fractional), select Format > Cells from the Worksheet menu, and select the new format on the Number tab in the Format Cells dialog box.

~~~~~~~~~~~~~~~~~~~~~~~~~

Entering Data in Spreadsheet Cells

Entering Data in Database Rows

Entering Formulas in Worksheet Cells

Use formulas to evaluate and perform operations on drawing data. Formulas always begin with an equal sign (=) and consist of a combination of functions, cell references, or operators that combine values to produce a new value.

Formulas must be entered with a specific syntax. If the formula is not entered correctly, the formula entry itself displays in the cell, instead of the result of the formula. Two common mistakes in syntax include forgetting to use pairs of parentheses, and omitting required commas when no argument is present. Formula syntax is described in the following table.

To force the program to treat a number as text, enclose the number in single quotation marks, as in '40'; or format the cell as Text on the Number tab of the Format Cells dialog box.

Formulas follow standard algebraic rules of hierarchy. In the following example, the value in cell C28 is first multiplied by 12, and then 4.5 is subtracted from that value. The result is then divided by 12.

=((C28*12)-4.5)/12

There are several built-in functions that can be used in formulas, including mathematical functions and functions that pull information from objects in the drawing. To use one or more worksheet functions in a formula, either enter the function manually, or use the Insert > Function and Insert > Criteria commands to select a function and selection criteria (if required) from dialog boxes. See “Worksheet Functions” on page 1340 for more information about how to use functions.

If there is a logic problem or calculation error with a formula, an error code displays in the cell.

To manually enter a formula:

1. Select the cell.

2. Enter an equal sign (=), and then enter the formula. The entries automatically display in the worksheet Formula bar. A formula can consist of functions, operators, cell references, and constant values.

3. When the formula is complete, click the green check mark or press Enter to validate the entry. To cancel an entry, click the red X or press Esc.

4. The formula executes as soon as the cell entry has been validated (Auto-recalc must be selected in the worksheet preferences; see “Preferences” on page 1321).

To enter a formula with the Function and Criteria commands:

1. Select the cell.

2. Enter an equal sign (=). The entry automatically displays in the worksheet Formula bar.

3. Select Insert > Function from the Worksheet menu.

The Select Function dialog box opens.

4. Select a function from the list and click OK.

The formula is placed in the worksheet Formula bar and the cursor is placed between the parentheses, awaiting an argument, if necessary.

5. Do one of the following:

•       If the function requires numbers or text, enter the argument between the parentheses and proceed to step 8.

•       If the function requires selection criteria, select Insert > Criteria from the Worksheet menu.

6. If an object is selected when the Criteria command is selected, the Paste Attributes dialog box opens. Otherwise, proceed to step 7.

Do one of the following:

•       To use attributes of the selected object as the only selection criteria, select the attributes and click OK. Proceed to step 8.

•       To specify other criteria, or to use attributes of other objects in the drawing, click the Custom button.

7. The Criteria dialog box opens. Set each of the three fields to the desired selection criteria. Click More Choices to specify additional criteria. Click Fewer Choices to remove added criteria. Click OK to add the criteria to the function argument.

8. When the formula is complete, click the green check mark or press Enter to validate the entry. To cancel the entry, click the red X or press Esc.

9. The formula executes as soon as the cell entry has been validated (Auto-recalc must be selected in the worksheet preferences; see “Preferences” on page 1321).

~~~~~~~~~~~~~~~~~~~~~~~~~

Entering Data in Spreadsheet Cells

Entering Data in Database Rows

Entering Constant Values in Worksheet Cells

Referencing Other Worksheet Cells

Worksheet Functions

Referencing Other Worksheet Cells

A formula can reference the contents of one or more other cells. The cells can be referenced within the current worksheet (internal references), or from another worksheet (external references) within the same file.

External references must include the full path name to the other worksheet. The following table shows the syntax for entering an external reference into a formula.

If the name of the worksheet contains spaces, the name must be enclosed with single quotes as in the following example: ='Appliance Schedule':A1

To update an external reference, select File > Recalculate from the Worksheet menu.

Relative and Absolute Cell References

Cell references in a worksheet can be either relative and absolute. When the formula that contains the reference is moved, an absolute reference always refers to the original cell address, while a relative reference changes depending on the location of the cell that contains the reference.

Use the dollar sign ($) character to indicate an absolute reference. The $ character locks the part of the cell reference it precedes, as described in the following table.

In the following example worksheet, the formula =AVERAGE(B1..B3) is in cell B4. If the formula were copied to cell E9, the formula would automatically be changed to =AVERAGE(E6..E8). Because the references are relative, both the column and row would change relative to the cell where the formula is placed—always indicating the three cells directly above the formula.

~~~~~~~~~~~~~~~~~~~~~~~~~

Entering Data in Spreadsheet Cells

Entering Data in Database Rows

Entering Formulas in Worksheet Cells

Entering Constant Values in Worksheet Cells

Entering Data in Database Rows

Database rows display data fields, calculations, or images associated with the objects in a drawing. The database header row is identified by a diamond shape next to the row number. When you create the database row, set the criteria to determine which objects will be listed in the related sub-rows. For example, you might set a header row to list all symbols in the drawing. A sub-row would then be generated for each symbol in the drawing. (If no object meets the header row criteria, no sub-rows are created.)

Many criteria combinations can be specified, such as class, object type, record information, or line weight. For example, create a list of all the rooms in a resort, or list only the green wing-backed chairs from all the two-room suites that are scattered throughout the resort.

In each column in the database header row, specify which information about the objects to display. A column can list a specific attribute of each sub-row object, such as its class or layer. A column can also list a data field contained in a record attached to each object. Or, a column can contain a constant, an image, or a formula, just as a spreadsheet cell can.

In the following example, database header row 2 has its criteria set to list all the objects in the drawing that have the appliance record attached to them. Columns A through D list the contents of the data fields in the appliance record: the appliance type, manufacturer, model number, and price. Column E contains a formula, which uses the value in column D to calculate the price of the appliance with sales tax. Column F lists which layer of the drawing contains the object.

To create a database row:

1. Right-click (Windows) or Ctrl-click (Mac) on the number of the row to change.

2. From the Worksheet row menu, select Database.

The Criteria dialog box opens.

3. Specify the selection criteria for which objects to display in the sub-rows. The number of objects that meet the criteria displays, to help you verify that the criteria is correct. To specify additional criteria, click More Choices.

4. Click OK to enable database functionality for the row. Beneath the header row, sub-rows are created for each drawing object that meets the criteria specified. The columns are empty until you define which data from the objects to display in each column.

5. Select each database header cell, and specify the information to be shown in each column of the row:

•       To list attributes of each object (such as layer or class), see “Retrieving Object Attributes in a Worksheet” on page 1337.

•       To list record data associated with each object (such as color or price), see “Retrieving Record Information in a Worksheet” on page 1338.

•       To show the results of a formula for each object, see “Entering Formulas in Worksheet Cells” on page 1331.

•       To show an image for each object, select Insert > Image Function from the Worksheet menu. Alternatively, select Insert Image Function from the context menu. See “Inserting Images in Worksheets” on page 1339.

6. Each sub-row cell displays the information requested. Each cell in the header row displays the total number of objects found, or, if the column returns numerical data, the header cell displays the sum for all sub-rows. Information found in each column can be sorted using the ascending, descending, and summarize buttons; see “Database Row Sort and Summary Functions” on page 1325.

Undefining a Database Row

To undefine a database row:

1. Right-click (Windows) or Ctrl-click (Mac) on the number of the database header row to change.

2. Select Spreadsheet.

Undefining a database row removes the database row criteria and all sub-rows. Any formulas that were defined in the columns of the header row remain intact.

Retrieving Object Attributes in a Worksheet

A drawing object can have several attributes, such as the layer it is on, the type of object it is, the symbol name (if it is a symbol), and whether it is currently selected. You can display this information in the database rows of a worksheet.

To retrieve object information in the database rows:

1. Click the cell in the database header row where the formula will be entered.

2. Enter an equal sign (=), and then enter the criteria to display. For example, enter =C to display the name of the class to which each object belongs. The entries display in the worksheet Formula bar.

3. Click the green check mark to validate the entry.

Retrieving Record Information in a Worksheet

Database records are created in the Record Formats dialog box. These records are then assigned to objects through the Data tab of the Object Info palette. See “Visualizando e Editando Registro de Objetos” on page 261 for more information. This information can be displayed in the database rows of a worksheet.

To retrieve record information in a database row:

1. Click the cell in the database header row where the formula will be entered.

2. Enter an equal sign (=), and then enter the record information to display. The entries display in the worksheet Formula bar. The syntax for retrieving record information is:

A period (.) must separate the two names, or the formula will not be executed.

If the name of the record format or field name contains spaces, the name must be enclosed with single quotes as in the following example: =‘Appliance Record’.‘Model Number’

3. Click the green check mark to validate the entry.

The database information attached to each object displays in the sub-rows.

Selecting and Editing Database Objects

You can use the database rows in a worksheet to select the object(s) in the drawing that are related to that row. If Vectorworks Design Series is installed, you can also edit the information associated with many database objects from the worksheet.

To select database objects:

1. Either all database objects or a single database object can be selected.

•       To select all database objects that meet the database row criteria, right-click (Windows) or Ctrl-click (Mac) the row number of the database header row to open the context menu.

•       To select an individual database object, right-click (Windows) or Ctrl-click (Mac) the row number of the sub-row that contains the object to open the context menu.

2. From the context menu, select either Select Data Items or Select Item.

All database objects that are represented by the header row, or the individual row object, are selected. If an individual object was selected with Select Item, the drawing view changes to display the selected object. The Select Item command is unavailable if the sub-row is summarized (see “Database Row Sort and Summary Functions” on page 1325).

To edit database objects:

In database sub-rows, some information can be edited, but some cannot. For example, the results of a calculation cannot be edited. However, if Vectorworks Design Series is installed, the data associated with a database object can be edited in the worksheet, and the object’s data record will be updated as well. For details, see “Editing Cell Contents” on page 1317.

~~~~~~~~~~~~~~~~~~~~~~~~~

Entering Constant Values in Worksheet Cells

Entering Formulas in Worksheet Cells

Worksheet Functions

Inserting Images in Worksheets

Inserting Images in Worksheets

If Vectorworks Design Series is installed, images can be added to worksheets to provide graphic views of the objects in a drawing. The images can be either thumbnail versions of objects, or samples of the 2D attributes of objects. For example, you might create a window schedule that shows top and front thumbnail views of each type of window in your drawing, in addition to data about each window.

Worksheets can have two types of rows: spreadsheet and database. The cells in a spreadsheet row contain constants (text or numbers), or formulas. Database rows consist of a header row and sub-rows, and they show data that are associated with specific drawing objects. Images can be inserted in either type of row.

To insert an image in a cell, use the image function, which works the same as other standard worksheet functions; see “Entering Formulas in Worksheet Cells” on page 1331.

Like other functions, you can enter an image function manually on the Formula bar, or use commands and dialog boxes to build a formula that uses the image function.

A cell that uses the image function in a formula can include either a text expression or an image, but not both.

To manually enter an image formula:

1. Select the cell.

2. Enter the image function (=IMAGE). The entry automatically displays in the worksheet Formula bar. If the cell is in a database header row, no further entry is required.

3. If the cell is in a spreadsheet row, enter the rest of the formula to specify which object to display. For example, to display an image of the symbol called K-02221, enter this:

=IMAGE(S='K-02221')

4. When the formula is complete, click the green check mark to validate the entry. To cancel an entry, click the red X.

5. The formula executes, and the image(s) display (Auto-recalc must be selected in the worksheet preferences).

6. Customize the image display as described in “Entering Data in Spreadsheet Cells” on page 1329.

To enter a formula with the Image Function and Criteria commands:

1. Select the cell.

2. Select Insert > Image Function from the Worksheet menu.

The image function is placed in the worksheet Formula bar and the cursor is placed between the parentheses, awaiting an argument. If the cell is in a database header row, no further entry is required.

3. If the cell is in a spreadsheet row, enter criteria to specify which drawing object to display. Select Insert > Criteria from the Worksheet menu.

4. If an object is selected when the Criteria command is selected, the Paste Attributes dialog box opens. Otherwise, proceed to step 5.

Do one of the following:

•       To use attributes of the selected object as the only selection criteria, select the attributes and click OK. Proceed to step 5.

•       To specify other criteria, or to use attributes of other objects in the drawing, click Custom.

5. The Criteria dialog box opens. For each set of criteria, select the choices that apply. Click More Choices to specify additional criteria sets. Click Fewer Choices to remove added criteria sets. Click OK to add the criteria to the function argument.

6. When the formula is complete, click the green check mark to validate the entry. To cancel the entry, click the red X.

7. The formula executes, and the image(s) display (Auto-recalc must be selected in the worksheet preferences).

8. Customize the image display as described in “Entering Data in Spreadsheet Cells” on page 1329.

Click here for a video tip about this topic (Internet access required).

Worksheet Functions

Worksheet functions take an argument, perform an action, and return a value or values. There are two basic types of functions: those that use the value(s) you enter, and those that use information from objects in the drawing. The arguments required by the two function types are different.

•  Number or text arguments: Functions that begin with a lower case letter typically require a number value or a cell address as the argument. For example, the acos function returns the arccosine of the value that is specified in the function argument. The argument you enter can be a mathematical expression (such as 3/5), an address of a cell that contains a number (such as A12), or an actual number. The argument for all trigonometry functions must be in radians.

•  Criteria arguments: Functions that begin with a capital letter must be applied to one or more specific objects in the drawing. In a cell in a database header row, a function is automatically applied to the object listed in each sub-row, so no criteria argument is required.

However, in a spreadsheet cell, you must enter criteria to select the objects the function applies to. For example, the Area function returns the combined area of all 2D objects that meet the criteria. To specify which objects to obtain the area of, either use the Insert > Criteria command on the Worksheet menu, or enter the criteria manually. For details about how to specify criteria such as the object type, class, or visibility, see the developer oriented documentation here:

http://developer.vectorworks.net/index.php/VS:Search_Criteria#Search_Criteria_Tables

http://developer.vectorworks.net/index.php/VS:Function_Reference_Appendix#attrCrit

The following table lists all of the worksheet functions available, as well as what kind of argument the function takes.

You may want to display an attribute associated with a drawing object in the worksheet (such as the object’s class, or which layer it is on); see “Retrieving Object Attributes in a Worksheet” on page 1337.

~~~~~~~~~~~~~~~~~~~~~~~~~

Entering Data in Spreadsheet Cells

Entering Data in Database Rows

Importing Worksheets

Data from a wide variety of worksheet formats can be imported into a file, including worksheet data from other programs and worksheets from other Vectorworks files.

Importing Worksheet Data from Another Program

To import a worksheet into the current file, a blank worksheet must exist in the active drawing as a destination for the imported worksheet. During import, the spreadsheet data is imported, but the formulas (logic) are not.

To import worksheet data into a Vectorworks file:

1. From the Resource Browser, select Resources > New Resource > Worksheet.

The Create Worksheet dialog box opens.

2. Specify the same number of rows and columns that are present in the worksheet to be imported.

3. Select File > Import > Import Worksheet.

4. Select the worksheet file to be imported. The worksheet must be in one of the following formats:

•       Tab delimited text (.txt)

•       Comma delimited text (.csv)

•       DIF (.dif)

•       SYLK (.slk)

5. Click Open. The worksheet is imported into the open worksheet in the Vectorworks file.

Importing Worksheets from Another Vectorworks File

To import a Vectorworks worksheet from another Vectorworks file:

1. From the Resource Browser, locate the file containing the worksheet using the Resources > Find command, or from a Favorites file. See “Accessing Existing Resources” on page 225 for more information.

2. Select the worksheet to import, and click Import. (Alternatively, drag the worksheet resource into the desired Vectorworks file to insert it as a drawing object; see “Worksheets as Graphic Objects” on page 1356.)

The selected worksheet is imported into the current file.

Importing a Table from Microsoft Word

The following procedure is an example of how to import a table from Microsoft Word into a Vectorworks worksheet.

To import a Word table into a worksheet:

1. In Word, select the entire table to be imported.

2. In versions of Word prior to 2007, select Table > Convert > Table to Text. In Word 2007 and later, select the Table Tools > Layout > Convert to Text.

The Convert Table to Text dialog box opens.

3. In the dialog box, click Tabs for the separation identifier.

4. Click OK.

5. Save the table.

If the file consists of only the table, select File > Save As. In the Save As dialog box that opens, select Text Only from the Save as type list, and click OK.

If the table is part of a larger file, copy the table to the Clipboard. Select File > New, and paste the table text into the new document. Save the file as a text file.

6. Return to the Vectorworks file and import the table as described in “Importing Worksheet Data from Another Program” on page 1355.

Exporting Worksheets

Vectorworks worksheet files can be exported and read by spreadsheet programs, such as Excel, as well as by some word processing programs. Because many of these programs have different format requirements, several export formats are available. These include comma delimited, tab delimited, merge, DIF, and SYLK. Using the tab delimited format, for example, creates a file that can be opened as a table in Microsoft Word.

To export a worksheet from a Vectorworks file:

1. Open the worksheet to export.

2. Select File > Export > Export Worksheet.

3. The Export Worksheet dialog box opens.

Select the format for the export, and whether to export all rows or only the selected rows. Click OK and specify the name and location of the exported worksheet.

4. Click Save. The specified worksheet rows are exported to the desired location in the set format.

Worksheets as Graphic Objects

A worksheet can be included in a drawing as a graphic object. The worksheet object can be moved to any location on the drawing, but it cannot be resized. Use the Attributes palette to modify the fill, pen, and line thickness attributes of the entire worksheet object. To edit the contents of the worksheet, open it from the Resource Browser, or from the drawing itself.

To include a worksheet as an object on a drawing:

1. From the Resource Browser, select the desired worksheet.

The worksheet must be a part of the current drawing. If the worksheet is part of another drawing, first import it into the current drawing. Select the worksheet from the Resource Browser and then select Resources > Import. (See “Importing Worksheet Data from Another Program” on page 1355.)

2. Select Resources > Worksheet On Drawing.

The worksheet is now included in the drawing as a graphic object. It can be moved like any other graphic object and placed where desired. If the worksheet is currently open, the contents of the worksheet in the drawing are replaced with an X.

Alternatively, double-click the worksheet in the Resource Browser to place it in the drawing file, or select the worksheet in the Resource Browser and drag it to the desired location in the drawing file. Both methods automatically select Worksheet on Drawing and place the worksheet as a graphic object. If you use the drag and drop method, the top left corner of the worksheet is placed at the point where the mouse is released in the drawing file.

To edit a worksheet included as a drawing object:

1. From the Resource Browser, select the desired worksheet.

2. Select Resources > Open.

Alternatively, double-click the worksheet object in the drawing.

3. The worksheet is opened and available for editing. The contents of the worksheet in the drawing are replaced with an X. Close the worksheet to display the worksheet contents in the drawing.

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

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 connection 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” on page 1313.

•  Create a schedule based on a common record format of a set of objects (wall data, in this example). This option allows you to select the criteria you wish to display from all the available object criteria. See “Creating Reports” on page 1312.

•  Start with a preformatted schedule and customize it to achieve your goals. See “Creating Schedules” on page 1858.

To create a blank worksheet:

1. From the Resources menu in the Resource Browser, select New Resource > Worksheet. The Create Worksheet dialog box opens.

2. Enter “Wall Area and Costs” as the name for the new worksheet, and click OK. 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 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 (Windows) or Ctrl-click (Mac) on the header box for row 3.

2. From the Row context menu, select Database. The Criteria dialog box opens.

3. Set the three criteria options as follows:

•       Type

•       is

•       Wall

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

•       Wall Style

•       is

•       Aspen Interior Walls

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

6. 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).

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 (Windows) or Ctrl-click (Mac) 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 as needed 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. Enter formulas for each column in the database header row cells. The database header row can be hidden before the worksheet is placed on the drawing.

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. Click the following cells and enter the formula shown to extract data for each item in the database. Be sure to include the equal sign (=) before each item.

•       In A3 enter =WALLSTYLENAME

•       In B3 enter =WALLAREA_GROSS

•       In C3 enter =WALLAREA_NET

•       In D3 enter =WALLTHICKNESS

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

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

Right-click (Windows) or Ctrl-click (Mac) 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 and click OK.

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

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

•  “Worksheet Functions” on page 1340

•  “Retrieving Object Attributes in a Worksheet” on page 1337

•  “Retrieving Record Information in a Worksheet” on page 1338

•  “Entering Formulas in Worksheet Cells” on page 1331

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 on the header box for row 3 to select it. Three icons display just below the Formula bar.

2. Click and drag the Summarize icon to the header box for column A, which contains wall styles.

3. The number of sub-rows is reduced to only two (one row per wall style). The numerical values in columns B, C, and D are now sums. While this is desired for the wall area gross and net columns, you probably want to show the thickness value as the thickness of an individual wall, rather than all walls combined. To do so, apply another Summarize icon to column D.

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.

•       In E2 enter Labor Cost

•       In F2 enter Material Cost

•       In G2 enter Taxes

•       In H2 enter Total Cost

2. Click the following cells and enter the formula shown to determine the cost sums. Be sure to include the equal sign (=) before each item.

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

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

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

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.

•       In B4 enter =B3

•       In C4 enter =C3

•       In E4 enter =E3

•       In F4 enter =F3

•       In G4 enter =G3

•       In H4 enter =H3

2. Select cells B4 and C4, right-click (Windows) or Ctrl-click (Mac), and select Format Cells from the context menu. On the Number tab, select Dimension Area and click OK.

3. Select cells E4 through H4, right-click (Windows) or Ctrl-click (Mac), and select Format Cells from the context menu. On the Number tab, select the options shown and click OK.

•       Select Decimal

•       In Dec. Places, enter 2

•       Select Use Commas

•       In Leader, enter $ (dollar sign)

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

5. Select row 2, right-click (Windows) or Ctrl-click (Mac), and select Insert Rows from the context menu to add an empty row between the schedule title and the column labels.

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

7. Select View > Database Headers and then View > Grid Lines to hide the database header row and grid lines in the table.

Step 8: Format the Worksheet

Finally, select cells and use the Format Cells command to format the worksheet as needed. 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.

See “Formatting Worksheet Cells” on page 1326 for details.