Jump to content
bc

WORKSHEET CREATION

Recommended Posts

5ace76ede2471_ScreenShot2018-04-11at12_59_21PM.thumb.png.624baed8c9ffc0e17eb61edfd7a1f1b6.png

 

Pat,

 

SEE ATTACHED IMAGE

My earlier posting on the community board led me to wonder:

Is there a function I could put in the Number column of the Window Schedule (or wherever) that would allow the Window Schedule to include the doors listed in the Exterior Door Schedule?  My architect likes to include them here because they are French and to be purchased from the mfg who makes the windows. Also for title 24, etc they can be overlooked by consultants.

Would desperately appreciate your suggestions and time.

The how to is just as needed as the what, I might add.  It seems that the criteria for the Widths and Heights, etc also needs to be changed?

Or is it best to just start out with a new worksheet newly created from scratch? Way beyond my ken, it would seem.

Thanks

 

Bradley Chase 

Edited by bc

Share this post


Link to post

i have grappled with this issue.  It seems to me like there is no way to mix windows and doors in a single spreadsheet.  To go around it I just used window objects and named them as doors (D prefix as opposed to W) - sliding window as sliding door and bi-parting casement for swinging doors.  This is exactly to have, like you said, all windows and doors by one manufacturer in one  spreadsheet.  The only issue I noticed is the sill details which are not compatible.  But so far on almost all the projects we have in the office this is what we are doing and it works fine.

In reality the whole division between window and door is an artificial one, given the current design trends what is needed is something like an opening type - there are just too many different types of door window solid panel items that just dont want to molded into traditional typology.

Share this post


Link to post

There are a couple ways to do it.  One easy.  One not.

 

There is no limit to the number of databases you can have on a worksheet.  So you can just start the window DB right under the door DB and use criteria to make sure you only select the doors you want.

 

OR

 

You could use IF/THEN/ELSE statements in the DB header to select from both windows and doors.

 

I highly recommend the first option.  :)

 

Let me know if you need examples.

Share this post


Link to post

I totally need examples.

Like the old John Muir Volkswagen Repair Manuals (A Step-by-Step Guide for the Complete Idiot).

Seriously.  The videos don't get me there.

I have no idea how anyone knows what these functions are or the syntax or where to find them or anything.

I would like to know how to.

I have played with appending my window schedule with a door schedule and the schedule was appended but included every door in the file and doesn't let one delete rows.

Thanks for your troubles.

bc

Edited by bc
typo

Share this post


Link to post

Wow Pat! That was heroic!

It's going to take tomorrow for me to fathom and get to back to my work computer to realize.

Yes I do plan on reusing this....and it will be worth the effort.

Thank you.

Bradley

Share this post


Link to post

Pat,

I am working with a cannibalization of the script for the combined numbers.

I have inserted "Height" where there used to be IDPrefix', 'Door'.'IDLabel and "Elevation" where there used to be IDPrefix', 'Window'.'IDLabel

 

Here's your Function for the NO. Column A:

=IF('Door'.'IDLabel'<>'', CONCAT('Door'.'IDPrefix', 'Door'.'IDLabel'), CONCAT('Window'.'IDPrefix', 'Window'.'IDLabel'))

 

Here's my function for Column F:

=IF('Door'.'IDLabel'<>'', CONCAT('Door'.'Height'), CONCAT('Window'.'Elevation'))

 

This seems to work but the results are decimals to the nth place:

 

5acfd01d63f0d_ScreenShot2018-04-12at2_14_49PM.thumb.png.d186528b21f95edcf59747fc09a17475.png

 

I have selected the Column F and specified it for Dimension but no joy.

Any thoughts?

Thanks

Edited by bc

Share this post


Link to post

CONCAT is the text field equivalent to addition. It will only work with things that are of type Text.

 

Both the Door.Height and Window.Elevation fields are already numbers. And it also looks like you only want to put one of them into the cell at a time, so you don't need the Concat at all.

 

Try:  =IF('Door'.'IDLabel'<>'', 'Door'.'Height', 'Window'.'Elevation')

 

Ask again when you need to.

Share this post


Link to post

OK I get the CONCAT thing thanks.  I pasted your formula and switched the column back to General but got this:5acfd38dafb84_ScreenShot2018-04-12at2_41_46PM.thumb.png.2fe8003a88fb809bf6fae797ccd61287.png

 

I also tried to remove the spaces after commas....not there yet.

I also tried thhis:  =IF('Door'.'IDLabel'<>'', 'Door'.('Height'), 'Window'. ('Elevation')) for the heck of it.

Any ideas?

Thanks

Share this post


Link to post

I just copied and pasted my formula into my worksheet and it worked fine. Possibly you got an extra carriage return/line end?

 

Try and copy it again being careful to copy just from the = to the last )

 

Or try and type it in rather than paste.

Share this post


Link to post

OK that worked and provided decimals and good-to-go after I switched it to Dimensions.

 

Or should I spell that "Dimentians" ?

 

Thanks .  This is above and beyond.

Now I am going to attempt to adjust the notes columns and see what kind of trouble I can get into.

Share this post


Link to post

Just getting back to my computer and reading the Pat's novel :-)

 

I agree with everything he said.  But I do want to point out that there is another easier way.  See example attached. 

 

While it lacks the ability to interleave windows and doors by ID number, it doesn't require IF/THEN/ELSE statements and the parenthesis salad that goes with them.

 

 

More_worksheet_ideas.vwx

Share this post


Link to post

Thanks Michael, I like that I am now The Pat. I knew you were a good dude. ;)

 

And Michael is completely correct, if you just want them to print together without necessarily having the two groups intermingled, putting different databases (with equivalent fields in each column) in sequential rows is a much easier way to accomplish the task.

 

I just had some time and a bee in my bonnet about teaching more about worksheets function and saw this thread as a good starting point to put a bunch of information down in a single spot.

Share this post


Link to post

Not sure if my typo was an extra "the" or not capitalizing it:  "The Pat"

 

 

Share this post


Link to post

Well he's got it down pat, I know that for sure, so for me he's Down Pat.

As far as having side-by-side columns for the IDs, isn't that what comes out of the box with Doors and Window Schedules?

AT any rate, I will be doing more study of this this weekend when I return.

Thanks you guys.

Share this post


Link to post

OK so I have got a schedule that works.

I duplicated it and began exploring the option of having individual Door and Window, height and width columns such that things could read a bit more separately and also with the "IF" mandate one cannot edit the fields.

See what I have done below:

5ad0fcbe32d6c_ScreenShot2018-04-13at11_53_28AM.thumb.png.ae413c5ad5978799c9d49fc2e4c491eb.png

 

 

Notice the columns are displaying the data correctly for the respective Door/Window except for one thing.

In the Door columns it shows 0" where there are windows. Likewise the Window columns show 0" where there are doors.

One could say this is logical because there isn't present the criteria need to display otherwise.

BUT I want the cells that  display 0" to display nothing, or maybe a dash (-).

So isn't it logical that wherever the Door column searched and found a Window it would not return anything?

Can I get this schedule to NOT show the 0"?

 

Current Cell functions:

Door Width               =(Door.Width)

Door Height             =(Door.Height)

Window Width        =(Window.Width)

Window Height       =(Window.Height)

 

 

Thanks

 

Bradley

 

 

Edited by bc

Share this post


Link to post

Something like

 

=IF(((PON='Door')), (Door.Height), ('-'))

 

or if you just wanted the height of the opening

 

=IF(((PON='Door')), (Door.Height), (Window.Height))

Share this post


Link to post
12 minutes ago, bc said:

So isn't it logical that wherever the Door column searched and found a Window it would not return anything?

Can I get this schedule to NOT show the 0"?

Michael is right, the only way to get them to display something other than zero is to use some form of IF which then eliminates your editabilty.

 

Is it illogical? Maybe, but it is the way it works. You have asked for the value of a number field in a record that is not attached to the object. Since it is a number the value that indicated that nothing is there happens to be zero. Would different functionality be better, probably. Is it going to happen soon, probably not.

 

Perhaps you need two versions of your worksheet.  One with the IF functions to mask the missing data and one without the IF functions to be editable.

 

 

Share this post


Link to post
4 minutes ago, Pat Stanford said:

Perhaps you need two versions of your worksheet.  One with the IF functions to mask the missing data and one without the IF functions to be editable.

 

YES.

Pro tip:  One version of worksheet for presentation and one for data management.  

Share this post


Link to post
11 minutes ago, Pat Stanford said:

Is it illogical? Maybe, but it is the way it works. You have asked for the value of a number field in a record that is not attached to the object. Since it is a number the value that indicated that nothing is there happens to be zero. Would different functionality be better, probably. Is it going to happen soon, probably not.

 

OK , I just thought that it was weird for a Door data column to be indicating ANYTHING in a Widow Row.

So that's the way it works.

Actually, I suppose it's no weirder than me combining the schedules in the first place!

Thanks again.

The movie continues.....

Share this post


Link to post

Wow guys , that is some serious Spreadsheet elaboration. 

I believe my question is super simple to everything that i going on in this tread, so I hope I will slip trough... 

 

I have 2D door symbols, like total amateur, all types have different names (i.e. 1.01 in 30cm wall etc) and I manage to count them, but I would like to know on which level they are. Actually I set search criteria to Level but It would be awesome to actually have it written in one of the cells. 

 

Thanks 

Jan

Share this post


Link to post

Put a formal of '=L' in one of the database headers and it will show you the layer.  '=C' will give you the class.

Share this post


Link to post

Awesome! Thank you very much!

And while we are at it.... Is there a list I could find with all search criteria? 

Share this post


Link to post

Not a really good list. I guess someone should make one.

 

There are three ways to bring data into a worksheet. You can use the normal worksheet functions. You can use the Record.Field syntax.  Both of these are available to choose from the database header row.

 

The third way is the use the function that are part of the Criteria. You can use the Insert Criteria menu item to get something close that will show you the "abbreviations" for a given criteria. They are listed in the Vectorscript Appendix also.

  • Like 1

Share this post


Link to post

Pat, 

This is an amazing thread! 

I have another door/window worksheet issue maybe you can shed light on...

Is there anyway to edit the width and height of doors directly from a worksheet? I don't understand why these 2 fields must be "read only". Why can't they behave like all the other Record.Field columns?

 

Thanks,
Matt

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.

×