Jump to content
Sign in to follow this  
domer1322

Simple embarrassing worksheet question

Recommended Posts

I'm embarrassed to ask for help with worksheets, which I seldom use (until now). Can someone point me in the right direction to do this: I have many 2D shapes (polygons, rectangles, circles) that represent areas of paving or concrete,or grass, mulch, etc. All I want to do is show a simple spreadsheet that computes the total area of each area of concrete, paving etc. I can figure out how to add them up once I get them in the spreadsheet. I need help with getting the areas from each shape into the spreadsheet. I know that each shape reports the area in the OIP, but I want to make a spreadsheet that automatically shows the changes to the areas as I manipulate the 2D shapes. Apparently, I can't read the manual vey well, because I've ben through it and still can't grasp how to get the shape areas into a spreadsheet.

Share this post


Link to post

How have you "coded" which of your polygons, rectangles, circles are paving or concrete,or grass, mulch, etc.? By name? By class?

It should be relatively easy to set up a worksheet database line that adds the areas of items of one name or class or whatever you have used to designate their material.

Share this post


Link to post

The way you get the objects into a worksheet so you can query them is called a Database Row.

Click and hold on a row header and set it to be a Database Row. A dialog box will pop up allowing you to set the criteria for the objects you want to show.

As Mitch says above, you will need some kind of way to identify which objects are which type. Class, Fill Color, Layer or Object Name are usually useful for this.

Once you get the criteria set, you will end up with a sub row (ie 4.1) for each object in the drawing that meets the criteria.

Now select a column in the database header row (4, not 4.1, 4.2, etc.) and type in the formula "=Area" without the quotes. Each row should now show the area of that object. You can then add other formulas in other columns to get as detailed of report as you need.

There are then some things cross the top of the worksheet that look like buttons. They are actually tiles that you drag to the column to use. The three tiles are Sort Up, Sort Down and Sum. The Sum tile will compress multiple sub-rows into a single sub-row and then adjust all the other columns to be the sum of off the compressed sub rows.

Say you have objects on a Layer called "Landscape" and objects in classes called "mulch", "grass", and "concrete" each with three shapes.

Create a worksheet with a database row with a criteria of Layer is Landscape.

Create columns with formulas of "=C" (to show the class of each object) and "=Area" to show the area of the object.

Now, select a cell in the database header row or sub-rows and drag a sort tile to the column header of the column showing the object classes. They will now be sorted so that all of the grass is together, all of the mulch is together and all of the concrete is together.

Now drag the SUM tile to the top of the column showing the classes. Now you should have three sub-rows, one each with the total area of mulch, grass and concrete.

If you need more help, let us know.

Share this post


Link to post

Class all the different polylines so that you can differentiate between them in the worksheet.

Create a new worksheet

Right-click on the row to contain the selection criteria and choose Database.

For the selection criteria, choose Type=Polyline and then click OK.

In the first cell on that row, type =C. This will return the class name of any objects that meet the criteria.

In the second column, type =CriteriaArea(). This will return the area of the items that meet the selection criteria.

Give me a shout if you need any further help with this.

Share this post


Link to post

Thanks ... very helpful. I got enough info to make it work, but what about this problem: if I have 20 areas of sod, I want the worksheet to identify each area by name. I could make 20 different classes titled "sod 1, sod 2, sod 3 etc, but this seems very rudimentary and time consuming. So .... how do I get the areas to have a label that is identified on the drawing and in the worksheet ? (without requiring a class for each area).

Share this post


Link to post

Why not redefine your polygons to be Spaces. Then you can label, count, report them at will.

Share this post


Link to post

another problem: my areas show up as 144 times the real area. For example, if I have an area that is 1 ft x 2 ft, it shows up as 288 sq ft (obviously 144 sq in = 1 ft is in play here). I figured out that changing the document's unit preference to "feet" instead of "feet and inches" fixes this problem, but I want the drawing to have "feet and inches" for dimensioning purposes. Any suggestions ?

Share this post


Link to post

and ... another problem: I noticed areas that are circles or rectangles do not show up in the worksheet calculation. When I convert them to polygons they show up and are part of the calculation. Is this an abnormality of VW or operator error ?

(I never dismiss the strong possibility of operator error).

Share this post


Link to post

bcd: I haven't used "spaces" much but after a few minutes of trial it looks like that might be the best answer to these issues. Thanks for the help.

Share this post


Link to post

another problem: my areas show up as 144 times the real area. For example, if I have an area that is 1 ft x 2 ft, it shows up as 288 sq ft (obviously 144 sq in = 1 ft is in play here). I figured out that changing the document's unit preference to "feet" instead of "feet and inches" fixes this problem, but I want the drawing to have "feet and inches" for dimensioning purposes. Any suggestions ?

As a spreadsheet you can do calculations on each cell so all you have to do is divide the area function (in the header row) by 144 to give you sq ft.

Share this post


Link to post

Instead of 20 different classes why not just use the top box on the Data pane of the OIP to give each area a Name and then include a column with a formula of =N.

The circles and rectangles don't show up because you used Tamsin's criteria of Type is Polyline and they are not polylines. Either add circle and rectangle to the criteria or pick a different criteria altogether.

The area is off by 144 because of feet/inches as you said. Just change the formula to be =area/144 and you will get what you need.

Spaces are also a good options.

Share this post


Link to post

the more I work on this, the more puzzling it gets ..... I'm now using space objects, and when I calculate the areas, the correct answer is now the database number that VW gives me, but divided by 288. If my units on the document are set to "feet and inches" and the OIP shows each area correctly in sq ft, why does the worksheet have to be divided by 288 ? (or by 144 as listed above ?) ... something is not right ......

Share this post


Link to post

It sounds like you have somehow doubled the objects. Check and make sure that you only have a single space in each location.

Are you using viewports? If so, it may be counting the spaces once for the object in the design layer and once for each instance shown in a viewport.

Share this post


Link to post

the more I work on this, the more puzzling it gets ..... I'm now using space objects, and when I calculate the areas, the correct answer is now the database number that VW gives me, but divided by 288. If my units on the document are set to "feet and inches" and the OIP shows each area correctly in sq ft, why does the worksheet have to be divided by 288 ? (or by 144 as listed above ?) ... something is not right ......

Note that "feet and inches" is only for display purposes but the document's primary unit for this display type is inches. So any values returned by functions are always in inches. Linear measurements would be divided by 12 to get feet and areas by 144 (12x12) to get square feet.

I do not use "space objects" but I suspect that the pio does the calculation internally depending on the display type and presents the result in the Object Info Palette as a parameter. Consequently, in a worksheet you would simply access the value of the parameter without further calculations.

Share this post


Link to post

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

 

7150 Riverwood Drive, Columbia, Maryland 21046, USA   |   Contact Us:   410-290-5114

 

© 2018 Vectorworks, Inc. All Rights Reserved. Vectorworks, Inc. is part of the Nemetschek Group.

×
×
  • Create New...