Worksheet Cell Functions

[Ozzie]

This formula in a Worksheet Cell not a Database

=AREA((C='Paving-TynongToppings') & (LOC='Loc1601'))/1000000*0.08

I do not think this gains info from within Symbols

So I have to use (InSymbol)- I think

Is this correct

=AREA(((INSYMBOL) & ((C='Paving-TynongToppings')) & (LOC='Loc1603')))/1000000*0.08

Assuming I am trying to get the area of the Tynong Toppings in Class Paving-TynongToppings where my Symbol ? a fold down clothesline is in a container Class ?Utilities-Clotheslines with inside the Symbol a closed polyline in the Class Paving-TynongToppings

So the question really is - if another Class is in a container Class do I have to call the container Class first

Or do I have to call the info from the closed poly in Class Paving-TynongToppings by naming the Symbols the closed polys are in

[Pat Stanford]

Are the polygons inside the symbols in the Paving class? If not the above won't work.

Your last version will only get you the area of objects with the Paving class.

I don't think there is a way in the worksheet to get the area of objects that are inside a container object of a certain class.

Are you using the same symbols in different classes? If not, you could edit the symbol and set the class of the poly's to something unique so you can get the area.

The other possibility is to write a vectorscript that could go through and get the information from inside each symbol.

Sorry.

[Ozzie]

Pat - thanks so much

I have a Symbol - a lift up Clothesline in Class - Utilities-Clotheslines

Inside the Symbol is a closed poly in Class - Paving-TynongToppings

Darrell

[Ozzie]

Pat,

Just tried

=AREA(((INSYMBOL) & (C='Paving-TynongToppings') & (LOC='Loc1603')))/1000000*0.08

Does not return the closed poly area within the symbol

The closed poly area is in Class - Paving-TynongToppings and the Symbol itself is in Class - Utilities-Clothesline

Just wondering when you use INSYMBOL if you have to name the symbol

I had a look in help and the example there is a bit different under Specialised Searches

Darrell

[Pat Stanford]

I just sent you a file that shows using the INSYMBOL. It is a generic command that makes the rest of the function look inside all symbols.

You definitely don't need to name the symbol.

I have the class inside a symbol working. I am still trying to figure out the LOC command.

After some additional playing with it, it appears that LOC and INSYMBOL don't play nice together. If you use LOC you get the area of the objects inside the location, but if you add INSYMBOL, you get the area inside all of the symbol instances.

I don't know of a work around other than to try and find a different criteria.

Maybe a script that attaches a record to the objects inside the LOC. No, that won't work because you want the objects inside the symbol as well.

Sorry.

[ccroft]

You may very well have thought of this and have reasons for not doing it, but if you convert your symbol to group then insymbol & C=' ' & loc=' ' will work. With the proper punctuation of course. You can get rid of insymbol altogether if you like.

If this is acceptable and you'll be using this in other drawings, you can edit the insertion options of the symbol and choose 'Convert to Group'. I have many symbols that convert to group for this and other reasons.

Pat: I was just fooling about with classed objects in symbols in a location and got a different result: nothing qualified anywhere. No areas reported....I wonder why?

[Pat Stanford]

I don't know. I will send you my test file off line for you to take a look at.

[Hugues]

Hi Ozzie,

Is 'Loc1603' a symbol ?

If yes , then this is the problem.

The LOC criteria is used to find objects that are located within the bounds of a named object. We have just found that it isn't working if that named object is a symbol. Looks like a bug.

If your polygon is within the bounds of another object in your symbol you can try with that one.

And also, use CRITERIAAREA instead of AREA to get the area in area units, so you can get rid of the divide by 1000000*0.08

Hugues

NNA

[Pat Stanford]

Hughes,

I think there is definitely a bug, but I think it is in the INSYMBOL criteria. I will send you the test file I created.

One polygon in a class. Three Instances of a symbol containing a poly in the same class.

Area((C='Class1')) give the area of the single poly.

Area(((C='Class1') & (LOC='Loc1603'))) give the area of a poly inside the poly named Loc1603 just as expected.

Area(((INSYMBOL) & (C='Class1'))) gives the area inside and outside of symbols as expected.

Area(((INSYMBOL) & (C='Class1') & (LOC='Loc1603'))) give the area of objects inside loc1603 and the area in all symbols placed in the drawing, not just the ones inside loc1603.

Regards,

Pat

[Hugues]

Hi Pat,

Thank you for investigating this.

There is definitely a problem with the LOC criteria when used on objects inside symbols.

Symbols have their own space and the coordinates and boundaries of objects in it are defined relative to that space.

What's happening here is that the poly in symbol returns its location relative to the symbol and this is checked against the location of loc1603 that is in a different space.

Currenly, the only way you would get accurate results with LOC and objects in symbols is by having your reference location in the same symbol.

Could you please submit a bug? We'll address it in the next release.

Cheers

Hugues

[Ozzie]

Hi all,

Thanks for all of your help with this and Pat thanks to you especially

CRITERIAAREA is the way to go to get rid of /1000000

This started because I had fold down Clothesline Symbols - different models for 'fixed to wall' and 'ground mount' and different again - some installed to lawn areas required a gravel and timber edging whilst others installed on top of gravel did not - 2 different models meaning overall lots of different symbols

So I was trying to count the Classed different elements within the symbols - very powerful if it works

No matter have gone back to two Symbols for each Clothesline and putting in my timber edging and gravel normally - all ok as I have to get this project out

Thing is though - 40 houses and all elements of the landscaping quantified down to kgs of lawn seed and starter, turf, mulch, topsoil, concrete for driveways and paths etc

Thanks again to you all - our distributor over will look at it

I have a wish - I have 11 A1 Sheets and on each sheet 3 or 4 houses with a Worksheet listing all Totals per lot and Totalling them into the right hand column

On my Cover Sheet I am using a Grand Total Worksheet which is the Totals for each Sheet above in seperate Columns to provide the Grand Total

Wish we could gain all the info from my 'Totals' Worksheet Columns with one formula into another 'Grand Total' Worksheet column for totalling purposes

Thanks again

[ccroft]

Ozz,

I'm not entirely sure if I understand your file structure, but it seems to me that you could reference the cells from the worksheets on each sheet into 'Grand Totals'.

If you're not familiar with worksheet referencing, the formula is: WorksheetName:cellPosition.

So Grand totals would have a worksheet cell with a formula that resembles:

=Sheet1:D3+Sheet2:D3+Sheet3+D3 and so on.

I use this sort of thing to reference totals out of a worksheet that contains a bunch of database rows into a 'Grand Totals' worksheet for further processing. The difference being that I'm reffing different cells in the same worksheet.

I don't see a reason why this wouldn't work with numerous worksheets.

One thing to watch is that this formula is very literal. IE if the worksheet name in the formula is the slightest bit different from the actual name of the worksheet being reff'd nothing happens. I set this up so long ago that I can't remember for certain, but I think this might extend to capitalization and spaces as well.

[Ozzie]

Thanks

That is what I did

Over 11 sheets it worked really well but is tedious and time consuming

I need to get my head around VS to streamline the process

[ccroft]

Ah...sorry for stating the obvious. I thought that maybe you were using the same criteria in 'Grand Totals' without limiting by layer and possibly running into problems with that.

11 worksheets with 10 or more tallies. That's a lot of cell referencing to 'Grand Totals'. I feel your pain. I have a system that does a similar amount of work for a cabinet shop, but I don't have to isolate by sheet. I assume you saved all that work in a template for future use and won't have to go thru all that again.

As for learning and implementing vScript, that has it's own special brand of torture if you're not already familiar with a programming language. I've written a couple of tools that run around the drawing gathering and processing info, and writing to worksheets as dumb text. You gain far greater control, and you can manipulate the data in ways that you simply cannot in a spreadsheet or database, but I'm not sure it's any less tedious up front.

A healthy interest in puzzle solving helps a lot in that endeavor. Seems to me you have that in spades.

[Ozzie]

Charles,

Thanks

11 Worksheets each containg 3 or 4 house lots where I was collecting my areas and counts etc - multiplying, dividing areas to gain volumes with each house lot surrounded by a location poly

Loc1, Loc2, Loc3 etc etc

The real work was in each individual cell assuming all info for Lot 1 is in Column B where I copied and pasted Column B to Column C - Lot 2 and then in each Column C Cell had to laboriusly change each instance of Loc1 to Loc2

Do this 38 times where I had 22 rows of info - lots of work

My little dream with this is a script which would query all instances of the text 'Loc1' in the cell formulas in Column say B - present a dialogue allowing the user to change Loc1 to Loc2 and replace all instances of Loc1 to Loc2 in Column say C

Or am I dreaming?

[Pat Stanford]

I don't know if you are dreaming or not, but take a look here:

http://techboard.vectorworks.net/ubbthreads/ubbthreads.php?ubb=showflat&Number=121895Post121895