• 0

# Custom Numbers In Calculated Worksheet

## Question

2.1 through 2.4 are calculated from Records. I want to add a column that is Extras Spares and then a sum Column that calculates the total.

Column 3, 4 are manually added as they are spares not on the plot.

I want to manually put in spare #s in column C that are calculated from records, but it won't let me. Is there a way to do this?

Also the =SUM isn't working for total units for 2.1 through 2.4. It's calculating as "1"

Help? thx

## Recommended Posts

• 0

Just guessing that column B is =COUNT?

So it is D is returning the value 1 (the count) + 0.  In column D check Sum Values in the little pulldown.

Is column C returning the value of a record?

##### Link to comment
• 0

in column C, he wants to manually add (or auto calc a percentage) spares

##### Link to comment
• 0

taking what michael said, how's this:

Edited by Mark Aceto
##### Link to comment
• 0
1 hour ago, trashcan said:

I want to manually put in spare #s in column C that are calculated from records, but it won't let me. Is there a way to do this?

the reason it won't let you manually enter spares in those rows of column C is because they're a database, so you either have to auto calc every row in column C (as in my screenshots) or add "dumb" spreadsheet rows below the database (which would quickly get annoying)

##### Link to comment
• 0

@michaelk is there a way to reference an external spreadsheet or database (like filemaker)?

##### Link to comment
• 0

@Mark Aceto you're right that a database is tricky.

But any non database cell can also count objects.  Instead of having a database with a criteria and then using the database header to call the count function, you can use a regular spread sheet cell, call the count function and put a criteria on the count function.

I'll attach an example.

##### Link to comment
• 0
1 hour ago, Mark Aceto said:

@michaelk is there a way to reference an external spreadsheet or database (like filemaker)?

Yes*

Vectorworks can communicate with external databases that use Open Database Connectivity and SQL standards.  The way I understand it that does include FileMaker Pro.

I have tried and failed to make it work :-).

I think the only person I've ever met who understands it is John McKernon.  I'm pretty sure that's the root of Lightwright.

Sounds like a good user group topic 🙂 .

##### Link to comment
• 0
47 minutes ago, michaelk said:

Sounds like a good user group topic 🙂 .

For context, there are 2 hopes:

1. I would like to take the worksheets out of VW where I can do so much more (pivot tables), so much faster (formatting), and then reference them back into VW
• This would also help me manage a larger library of worksheets
• If nothing else, I might just export them, play with them, and then bring them back in
• However, it seems like the database header row formulas get lost in the plot export
2. The shop wants to streamline shop orders across multiple projects
• I caveated the dream of a magic bullet shooting for the moon but there's probably a happy medium that will at least improve quality of life for them
##### Link to comment
• 0

You guys are the best!

@michaelk Do a manual spreadsheet rather than a report w/ database rows works perfectly.

I have fixed number of spares, rather than a percentage, so instead of referencing a percentage cell, I'm just putting in a number. All of my objects that I'm counting are symbols so that works great.

I don't understand the "'Don''t Count These'" in the formula and the "L" - my stuff still calcs properly with this, but wondering what this is.

##### Link to comment
• 0

Typing in a number is how I do it with lighting instrument inventories too.  But if it's something like tile for a shower or steel for the framing of a flat I'll do some percentage of waste to get total quantity of material needed.  I was riffing of of Mark's idea of a changeable percentage of spares.

There is no way for a worksheet to "see" a symbol that is in the resource manager, but not inserted into the drawing.

To get the image of a projector that isn't being used you need a dummy insertion.  Hence a layer of all symbols used in the inventory in a "Don't Count These" layer.

What I find helpful when making these kind of inventory worksheets is to create a temporary database of objects in the layer "Don't Count These".  In the database header put =S to get the symbol names.  Then you can copy and paste them into the criteria when making the real inventory.  When getting a count, exclude that layer.

##### Link to comment
• 0

@Mark Aceto aha - so "Don't Count These" is a Layer - "L"= Layer

##### Link to comment
• 0

It's the old school way, before autocomplete or pulldowns with choices in the database headers.  Just like you could wake me at 4:00 am and ask me for all the German dative prepositions, you could also ask me for the list of database header abbreviations.

=L Layer

=C Class

=T Type

=PON Parametric object name

=S Symbol

etc.

That list has disappeared from the Help pages (or I can't find it) so those may be in the process of being deprecated.  Not sure.

##### Link to comment
• 0
19 minutes ago, michaelk said:

That list has disappeared from the Help pages (or I can't find it)

Here?:

21 minutes ago, michaelk said:

=PON Parametric object name

I always wondered what PON stood for!!! Duh...

##### Link to comment
• 0
19 hours ago, michaelk said:

Vectorworks can communicate with external databases that use Open Database Connectivity and SQL standards.  The way I understand it that does include FileMaker Pro.

I have tried and failed to make it work :-).

I think the only person I've ever met who understands it is John McKernon.  I'm pretty sure that's the root of Lightwright.

Sounds like a good user group topic 🙂 .

I'm not sure even John understands ODBC.  He might, but Lightwright uses an XML file to communicate with VW.  XML files are blazingly fast, but require coding both from LW and VW to work.  The only person I know of that has come close to making ODBC work is Carlotta who is on the Architecture and Vectorscript forums, and she considered it too fragile to use.

In the absences of getting ODBC working you are left with using scripting to communicate with external flat file databases via tab delimited exports or writing to XML files.  Both require the external database to write to a file as well as VW.

If somebody figures this out, it would fulfill a dream I've had for 25 years.

##### Link to comment
• 0

I didn't know that John was using XML.

When I've looked into it it was the getting data back into VW that made my brain hurt.

• 0

There it is!

##### Link to comment
• 0

@michaelk and @Sam Jones I thought there was an option to reference an external spreadsheet on import...

##### Link to comment
• 0

You can import a spread sheet and you can reference another worksheet in the drawing.  And I bet Sam knows how to reference worksheets in other drawings.

##### Link to comment
• 0
3 hours ago, trashcan said:

@michaelk Do a manual spreadsheet rather than a report w/ database rows works perfectly.

@michaelkis there a risk of user error with this method? I always think of VW database rows as dumb pivot tables. For example, if I add a new object that needs to be counted, the DB will automatically find it, recalc, and expand the number of rows. However, for a spreadsheet (not a database), do I have to manually add that object?

##### Link to comment
• 0
2 minutes ago, michaelk said:

You can import a spread sheet and you can reference another worksheet in the drawing.  And I bet Sam knows how to reference worksheets in other drawings.

The goal is to take the worksheet editing and formatting out of VW. Also curious how VW manages XLSX tabs because I typically use Excel/Sheets as a "database". First tab is always "Entry", last tab is "Validation", and then I'll create a bunch of tabs in between that are pivot tables rearranging the data.

##### Link to comment
• 0
21 hours ago, michaelk said:

@trashcansave these scripts

##### Link to comment
• 0

I'm expecting that soon someone will tell me how =RunScriptEdit is really supposed to work and I'll make better ones 🙂

##### Link to comment
• 0
2 hours ago, Mark Aceto said:

@michaelkis there a risk of user error with this method? I always think of VW database rows as dumb pivot tables. For example, if I add a new object that needs to be counted, the DB will automatically find it, recalc, and expand the number of rows. However, for a spreadsheet (not a database), do I have to manually add that object?

Yes.

All these issues are what drove me to create EL Dooro and EL Window 🙂

##### Link to comment
• 0
44 minutes ago, michaelk said:

EL Dooro

Officially submitting a VE for “El Doorado”.

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

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

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

×
×

• KBASE
• #### MARIONETTE

×
• Create New...