Jump to content
rowbear97

Worksheets

Recommended Posts

I'm clearly out of my league here!

 

I've looked around and done a little experimentation yet I am no where closer to finding a solution. I'm working on a project where I want to inventory symbols. In this worksheet I have created criteria that counts all the symbols and sums up the symbols with the same name giving me a count that is accurate. Here is the tricky part. I want to take the inventory of different symbols by name and add them together as well as multiply one particular symbol by two to give me a final total number of units.

 

What am I missing or is this a functionality that doesn't exist?

worksheet calcs.jpg

Share this post


Link to post

The easiest way to do this is probably to add an extra column with the formula for the multiplication. You could then hide the original qty column if you don't want to see it twice.

 

Set the function in the Column C database header to something like:

 

=If((A2='2 Over 2 A'),B2*2,B2)

 

In English, what this is saying is If the data in the database sub row column A is equal to the value '2 Over 2 A) then multiple the quantity in column B by 2 otherwise just use the value in column B. The stuff before the first comma is the criteria.

 

If you have multiple objects that require the multiplication you can use a logical OR function (which is indicated by a vertical bar character) to add additional values.

 

((A2='2 Over 2 A') | (A2='2 Over 2 B'))

 

If you had some items that need to be multiplied by different values, you would need to use nested IF statements.

 

I believe that a formula must be less than 255 characters, so the above options are probably not very good for lots of different values.

 

Let us know if you need more help.

 

 

 

Share this post


Link to post

Pat

 

I think they took away the formula character limit.  Or at least made it big enough that it's effectively unlimited.  Just tried it with about 900 characters and it works.

 

 

mk

Share this post


Link to post

That is good, but trying to manage a formula with 10 Ors and 10 nested IFs is still going to be really nasty.

 

Probably better for something like that would be a simple RunScript with a CASE to do the lookup of the multiplier.

 

Since we don't know all of the actual requirements, I was just trying to provide some options.

Share this post


Link to post

Agreed!  It would be unreadable once it got that long.

 

I wonder if you could simplify it with a worksheet script in the header cell?  Certainly all the IF THEN ELSE statements would be easier to read.

 

 

mk

Share this post


Link to post

You can definitely run a worksheet script from a Database Header. The script it executed once for each subrow of the database. Actually there is a bug that should be fixed in 2017 SP3 where the script actually executes one extra time.

 

The =WSScript_GetObject procedure returns a handle to the object pointed to by the subrow.  

 

I have not played with it enough yet to know what happens when you SUM rows. I would assume it would run for each object and return the sum, but who knows.

Share this post


Link to post

Thanks for getting back to me. I really need to get my understanding of worksheets up to speed. I've tried what you suggested and still can't get the result I want. I'm going to use an alternate method for the time being as I have a deadline that I'm trying to meet.

Again thanks for your time.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

 

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.

×