1803 – Worksheets

Contents

• Beginner Topics
  • What is a Worksheet?
  • How do Worksheets work?
  • What types of Worksheets are there?
  • Creating a Worksheet to calculate areas (site coverage)
  • Creating a Worksheet for objects with data attached (plants)
• Intermediate Topics
  • Impermeable surface calculations
  • E2 risk matrix
• Advanced Topics
  • Finding objects in a room

Beginner Topics

What is a Worksheet?

Worksheets are like simple spreadsheets. Worksheets allow you to calculate and report information in your Vectorworks files. This is a powerful technique. It has been available in Vectorworks for many years.
You can use worksheets to find objects, report them, and do calculations on the objects that have been found.

How do Worksheets work?

Worksheets are set up to look for specific information. It uses Criteria to define what the search looks for. When Vectorworks finds that information it fills in the worksheet.

When you are setting up the worksheets, it is important to plan your report. There are so many options—you have to have some idea of how you are going to find the information in your file. For example, if you are creating a report to find the site area, you could look for a property line object. If there is more than one property line, then you might look for one on a particular class. You could name the object and use the name to find it. As you can see, even with a simple object, there are so many choices.  It is best to think about all of this before you get started.

What types of Worksheets are there?

• We can classify the worksheets into a few different groups depending on the nature of the worksheets:
• Count/select objects (generally symbols) throughout the file. They do not need to have a record attached or be in the same Class or Layer.  We can choose to count symbols on a specific layer or assigned to a specific class.
• Do mathematical operations with the parameters of drawn objects: areas, perimeters, volume, etc. Name the objects (Object Info palette) and find their properties and their combinations
• Create reports using symbols with records and list the field values from the symbols in the report.

The most powerful worksheets in Vectorworks are databases linked to Symbols or Plug-in Objects listing the data entered in different fields. As you add these objects into the file, you can update the worksheet and check the information. An example of this would be a bracing spreadsheet that tracks the bracing objects in a drawing. As you add bracing objects, the worksheet tracks the number, type and length of the brace and puts this information into the worksheet, telling you if you have achieved enough bracing in each direction. Another example is a plant report. As plants are added to the design, they will automatically be added to the report when it is updated.
One of the powerful things about worksheets is that they are a resource that can be copied from one file to another. Although a worksheet often takes a lot of work to create, the time taken is well spent because you can use it on multiple projects.

Creating a Worksheet to calculate areas (site coverage)

Worksheets in Vectorworks are powerful and can be programmed to search for information in different ways. We can search for information by pen color, weight, line style, class, layer, kind, etc. You can even search for named objects.
In this file, I have a site plan (property line) and a house plan (the rectangle).

• Select the site outline and open the Object Info palette.
• Assign the property line to the correct class (Site-Boundary). If the class does not exist, create it.

• Select the rectangle.
• On the Object Info palette name the object House.

We want to build a worksheet that will find these two elements, report their areas in a useful format and calculate the site coverage. In this case, we will be using the name of the house object and the object type and class for the site area.
[ms-protect-content id=”34491,34492,34493, 34494, 34495, 34496, 344927″]

• Make sure that the Resource Manager is open (from the Window Menu).
• At the bottom of the Resource Manager choose New Resource, then choose Worksheet.
• Click on the Create.
• Make the worksheet 5 rows high and 2 columns wide.
• Name the worksheet Site Area Calculation.

The worksheet will open in a separate floating window on the screen. The worksheet window may be too small to allow you to see all the cells.

• Move your mouse to the bottom right-hand corner.
• Click and drag the worksheet window to make it bigger.
• Start at the top left cell, click here—this is cell A1.
• Type in Site Area Coverage.
• Click on the green tick. 

Vectorworks will accept what you have typed in.  If you make a mistake, click on the red cross, Vectorworks will revert to whatever was there before you did any typing.

• Leave a row blank and move down to cell A3.
• Type in Site Area.
• Click on the green tick.
• Go to cell A4 and type in Building Area.
• Click on the green tick.
• In cell A5, type in % Cover.
• Click on the green tick.

You may find that the text spills out of the cells and into the next cell.

• To make a cell wider, move your cursor to the grid labels. If you move the cursor between two cell labels the cursor changes, and you can drag the divider to make cells wider or narrower.
• Click in cell B3, this is where we will be putting our formula to find the site area.
• On the worksheet window go the Menu bar, choose Insert > Paste Function…
• This will display a list of all the worksheet functions.
• Scroll down and choose Area.
• Click on the OK.

• Make sure that your cursor in the worksheet window is between the brackets after the word Area.
• Back to the Insert menu, choose Paste Criteria… Criteria are the search filters that Vectorworks will use to find information in your file.
• If the Paste Attributes dialog box opens then click on the Custom Button, we need the custom attributes.
• We need to choose the Type in the first drop-down menu.
• Click on the right-hand drop-down menu, choose Property Line.

• Click on the OK button.

Now your worksheet window will have the area and the name. It is not a formula yet, so Vectorworks will not yet put in the number.

• Put an = in front of the area, and it will become a formula.
• Click on the green tick to see the result.

• Right mouse click on the cell that you want to edit and choose Format Cells…

• From the Number tab choose the Dimension Area option. This will use area settings as the Unit for this cell.

• Format Cells… can be used to set the font, borders, alignment of the text and the fill color for cells.

• Click in cell B4—this is where we will be putting our formula to find the house area.
• On the worksheet window go the Menu bar, choose Insert > Paste Function…
• This display a list of all the worksheet functions.
• Scroll down and choose Area.
• Click on the Done.
• Make sure that your cursor in the worksheet window is between the brackets after the word Area.
• Back to the Insert menu, choose Criteria…

• If the Paste Attributes dialog box opens, then click on the Custom Button—we need the custom attributes.
• We need to choose Name in the first drop-down menu.
• Click on the little button at the right.
• This will open a list of all the names used in your file including named objects.
• Select House.

• Click on the OK button.
• Click on the OK button.
• Now your worksheet window will have the area and the name, but it is not a formula yet—so Vectorworks will not put the number in yet.
• Put an = in front of the area, and it will become a formula.
• Format the cell (as we did with the site area) to add the Dimension Area units.

Now for the site coverage calculation:

• Click in cell B5
• Put an =.
• Click in cell B4.
• Type in a divide sign (/).
• Click in cell B3
• Format the number as a Percentage.

The completed Worksheet

• Now the worksheet is completed. At the moment it won’t print on the drawing. We’ve done all the calculations that we wanted, so we can close the worksheet window by clicking on the close box on the title bar or on the worksheet.
• Right mouse click on the worksheet on the Resource Manager.
• Choose the option Worksheet On Drawing.
• The worksheet will not automatically update if you change the area of the house. But, if you right-click on the worksheet on the drawing, there is a Recalculate… command.  This will update the worksheet to show any changes made.

• The graphic attributes of the worksheet are controlled by the Attributes palette (line weight, fill color, etc.).
• You can select the worksheet and drag it to a new position on the screen.

Creating a Worksheet for objects with data attached (plants)

I call this the database method because it uses the data attached to objects.
In this example, I will be using a plant list that will find plants. This technique can be applied to any object that has data attached, not just plants. Previous manuals used this technique to look for doors or windows on an architectural plan. This is one of the most powerful techniques for creating worksheets: You can search for the information, there’s a database attached, and you can filter the information based on the database results. For example, we might want to have a plant list that only counts trees. We could first search for objects that have the plant database attached and then add an additional criteria that looks only for plants that are in the category “Trees.”
Another example would be that you can create a report that looks for all windows in a project. You could then report all the information that you require from each window, such as window number, window size, lintel size, opening configuration, etc.
Vectorworks has a premade plant list that will look for all the plants in your file. In this example, I will not be using that plant list. I will show you how to create your own. I only do this so that you can see how one of these reports is made. The most efficient way to create your plant list is to use the premade one from Vectorworks.
This example will create a new worksheet. Instead of using the Resource Manager to create the worksheet, we will use a command from the menu bar.

• Go to the Menu bar.
• Choose Tools > Reports > Create Report…

• This is where you could choose the preformatted report.

• As I explained earlier, we will not be using a preformatted report. We will be creating one from scratch.
• Name the report.
• Choose Basic Criteria.
• Choose Objects with a record.
• Click on the drop-down menu to the right.
• Choose Plant Record. There might be a long list of options, so make sure that you choose the correct one.

• There is a second area in which you can choose the record that you want to report. So you can use one record to find the objects and a second record to report the information. In this case, we will use the same record for finding the objects and for reporting.
• In the Columns area choose Plant Record from the drop-down menu.
• When you have chosen Plant Record, the list at the bottom left will populate with all the available fields that can be reported.
• In the left-hand area, choose the first field that you want to report. In this case, I have chosen ID.
• Now click on the Add>> button. This will add the ID field to the right-hand area. The right-hand area shows other columns that will be in your worksheet.

• Choose the next field that you want to add to your report and click on the Add>> button again.

• Continue adding the columns that you require.

• When you have all the required columns, you can choose to summarize items that have the same information. In this example, I have chosen to summarize items that have the same plant record ID. When I have completed this, I will end up with a list of the different plant types that I have, rather than a listing of each individual plant.
• You can also choose to append this new report to an existing one. At the bottom, check the option to append to the existing report and choose the landscape budget report.

• When we finish, we will have a combined report that includes the landscape areas, the hardscape areas, and the plants.

• Now that we have our plant list, it is clear that we do not have an area to count up all the plants. I could create a column at the end that counted all my plants, but I prefer to have my column at the beginning. Because we have appended this to an existing report we cannot add a column at the beginning, that would affect all of the landscape and hardscape areas as well.
• You can copy and paste information from one column to another to gradually move the columns across.
• Start with column D, the last column. Copy and paste the title of the column and the database header (where the number “7” is with the drop-down menu) from column D to column E.

• Repeat this again to copy the information from column C to column D.

• When you are finished, you will have the information in column A and column B repeated. Column A is the one that we want to change.
• Change the title of column A to Quantity. This is where we will count up the number of plants.
• Click on the the drop-down menu (where my report is currently showing number “7”). Click on the drop-down menu to change between records and functions.

• Click on the drop-down menu to choose the function called Count. This function will count up the number of plants, giving us the quantity of each type of plant.

• Turn off the option to Summarize Items and turn on the option to Sum Values.

• There is nothing to group my plants together in my report. It would be common to use the ID to summarize all the plants together.
• Click on the drop-down menu on the database header for the ID column.
• Choose to Summarize Items for this column.
• Click on the OK button.

• Now we should end up with the completed report with all of the plants grouped together by ID type.

Intermediate Topics

Impermeable surface calculations

This is a common report that is needed to show the total area of the site that has impermeable surfaces (paving, building, roads, decks, patios, etc.).
This worksheet is a development of the first exercise. Instead of just looking for two parts (the site and the house), we need to look for several parts (site, building, roads, paving, patios). In some locations, this worksheet is required by city planning. In Auckland, New Zealand, this is required for building approval.
Here is the definition from the Auckland City Council unitary plan:

The first step would be to design your report. We need to calculate the roof area (projected onto the site plan), paved areas, paved courts, roads, and engineered layers. The first option might be to use the roof object for the projected area of the building, the property line for the site area, and a single class for all the impervious areas. While this might work in some situations, there might be other situations where we would want to see the road but not other paved areas, or the patios (decks) but not the road. Therefore, will need to have more than one class to help us to create the drawings.
As I said earlier, the first step is to design your report.

In this case, we might use classes to find the parts for the report. For example, I will be creating paving, roading, and hardscape classes. The strategy is to work out how many parts are needed to create the report and whether you need a separate class for each part:

• The class for Roading could be Site-Roads.
• The class for Hardscapes could be Site-Hardscape.
• The class for Paving could be Site-Impermeable.
• The class for Pools could be Site-Pool.
• The class for Buildings could be Site-Building coverage.
• The class for Property Lines could be Site-boundary.
• The class for Patios could be Site-Decking.

 

• The first thing to do then is to create the classes and assign a graphic style for each class.

• Create the worksheet. From our sketch, we can work out that we need a report with 2 columns and 8 rows.

• When you have created the worksheet, fill in the title, format it, and fill in the titles for all the other areas.
• If you get to the point where your worksheet is either too large (too many columns and rows) or too small (not enough columns or rows) you can adjust the worksheet to suit: Move your cursor to the bottom right-hand corner of your worksheet (the cursor will change to a strange right-angle icon), then click and drag your cursor to make the worksheet larger or smaller.

• Move to cell B2.
• We have decided to use classes to find the information that we want. We can have objects throughout the drawing on the same class, and Vectorworks will find them all.
• Start by entering “=Area().”
• Then go to the worksheet menu, and choose Insert > Criteria…
• If you see the dialogue box with the Custom… button at the bottom right-hand corner, click on it. You will only see this if you already have an object selected.
• On the left-hand drop-down menu, choose Class and on the right-hand drop-down menu choose the class for your site boundary.
• Turn off the option to find objects inside design layer viewports, both referenced and non-referenced.
• Check to ensure that there is only one object that meets the criteria. If there is more than one object that meets your criteria that means you that have more than one site boundary. Your calculation for site coverage will be incorrect. You will have to locate the extra object and change it to a different class, or delete it.

• Remember to format the cell to show the area units.
• Repeat this procedure for the rest of the areas that you need to calculate.
• You will find that you have used some of your classes on more than one layer. In this case, you can always add the layer to your criteria to ensure that you find only the objects that you truly need.

• Complete the rest of the area calculations.
• Use the SUM formula to add up all the impermeable areas.

• Then complete the report with the percentage calculation.

E2 risk matrix

This is a local example to show how you can take a building requirement and make that into a worksheet. Projects in New Zealand need to have a risk matrix for weather tightness to show whether the building is required to have a ventilated cavity or not. This risk matrix is given in an Acceptable Solution (E2/AS1).

In situations like this, it is often useful to create a worksheet that can be incorporated into your drawings rather than using another spreadsheet program or a photocopy of the original document.
The aim is to make the worksheet look like the table shown in the Acceptable Solution.
This looks like a complex worksheet to set up because there are so many parts. It does have a lot of parts, but the mathematics is simple: add up the rows and sum up the column at the end. The most difficult part of the worksheet is formatting the worksheet to look like this.
Start by counting how many rows and columns will be needed. It is easy to get this wrong, remember to count the maximum number of rows, rather than counting the rows across the headings.
I counted 9 rows and 10 columns.

• Create the worksheet with the required number of rows and columns.
• Select all the cells.
• Right click on one cell and choose Format Cells…
• Select the required font and text size.
• If you want to replicate this report exactly, then add a light grey fill to all the cells and add a white border to the cells that need it. I didn’t try to copy it exactly—I use a white fill with black lines.
• Start by getting most of the cells approximately the correct width.

• All worksheets start with a grid. It would be easier to read the report if we removed the standard grid and replaced it with borders to match the original table.
• On the worksheet menu choose File > Preferences…
• Turn off Show Grid.

• Format the cells to have the lines around the cells required.

• Fill in the information and calculations for the report. This is not a tricky worksheet. Only some cells have numbers in them, and the last column just totals up these cells.

Advanced Topics

Finding objects in a room

You can report objects based on the layer, class, line weight, line color, fill style, etc. You can use a combination of criteria. This allows you to create sophisticated search criteria for your objects. One of the less common criteria is called Location.
Location looks for a polygon with a name and determines if the object is located inside the polygon.
When you create your report, make sure that you use the Location option and then you can choose the name of your polygon. In this example, I named each polygon to represent a room number—this was to make it easy for me to remember what the polygon names were.
This image shows the criteria for finding my objects. Notice that I had a specific layer for all of my furniture and that I had an Asset Database record attached to each of my objects.

You can name objects by using the Object Info palette.

Any objects that I move inside the polygon for Room 1 will show up on my report when it is recalculated. If I move the objects from Room 1 to Room 2, you will see them move on the report from Room 1 to Room 2.
[/ms-protect-content]