Anyone happy with User Templates? How good? 2

[IJR]

I have discovered that much routine work involving fix kind of excel data can be handled by setting up a template with all formulas and functions and routines in it and space for input data.

Seems interesting to me, but I need words of support

Any exchange of ideas on the subject is welcome

Respects
IJR

 

[Mala]

Personally, I prefer not to make templates - they tend to tie you up with a particular way of working. Something that may have looked appealing to you at the time of creation may not appear so (by a long shot) when u're ready to use it again. This is especially true for the kind of people on the eng-tips forums. You ultimately end up doing a lot of editing.
In my opinion, a better way (I'm talking about engg. spreadsheets) is to start afresh, copy the relevant portions (ranges, VBA code, etc.) from where they reside in previously created workbooks and get down to work.

 

[JAE]

We use spreadsheets as per Mala's comments but we also use a LOT of templates. So much of our work in the design of buildings is based upon calculations that are somewhat repetitive. For example, we have numerous templates that simply determine the wind loads applied to buidlings per UBC, ASCE7-93, ASCE7-95, etc. Same for snow loads, and we even developed a template for UBC 97 Seismic.

Also, design spreadsheets for steel, connections, wood, Concrete, etc. which can be set up to take a wide range of conditions and input.

The key is to be very careful with their development, cross check, compare to sample calculations, etc. to verify quality and correctness. Even with that, always review and spot check answers by hand.

They save an enormous amount of time....and today...time seems to be one of the most critical issues in a successful project.

 

[Mala]

Dear JAE
I think use of add-ins is more appropriate in the part of the context you mention (i.e. retrieving data from standard tables etc.). Add-ins have the added advantage that they work in the background and do not get in the way - u simply uses user-defined functions to retrieve values from the standard tables fed into the add-in. Thus the data doesn't get repeated in different workbooks - saves u space
Mala

 

[JAE]

I'm not sure I understand your term "add-ins". Our templates do not take much space at all as we don't save the spreadsheet for each and every use. For example, when starting up a new project, we need to calculate the required wind loads that the particular building code requires. We bring up the previously created spreadsheet template (which has specified areas of required input such as wind speed, exposure, etc.). We enter the input, the built-in functions calculate the relevent wind loads for walls, roofs, towers, etc. and we print it out to include in our calculations.

In this case, there is no standard "table", just a series of equations, if-then checks, and a nice display of the input/output.

Another sheet we use calculates the capacity of a standard double angle connection using AISC tables 9-2, 9-3 and 9-4. We didn't enter the AISC tabular capacity values. Instead, we designed the spreadsheet to actually do the calculations for each type of beam/angle combination. The beam data is on an attached worksheet that includes all the relevent shape data. Again, this doesn't take up a lot of space on the disk as we don't save repeated copies of the template. By printing out the template for each use, and including all the input data, we can re-create the same sheet later, if needed.

One other valuable benefit from using this spreadsheet template that I didn't mention before: To actually write a spreadsheet (or a program) one must have a very exhaustive understanding of the function you are trying to develop. In writing a wind load template, for example, I came away from the effort with a much better knowledge base of the required code provisions and design processes. This is because the template must apply to a number of different uses, condition, input values, etc., and therefore must be written to encompass the full logical process required. All of the what-ifs, if-thens, must be understood. Great self-learning tool!

 

[yakpol]

JAE, it will be great to see your templates! Post them on structuralist.net or I can place them on my site, unless it's a company property and some legal obstacles on the way..

yakpol

 

[IJR]

JAE

Thanks for clearly explaining your template deals. The type of application you make is what prompted me to post this thread. Seems you are happy with templates idea. So I will pursue it.

Your point of view that writing a comprehensive spreadsheet takes time to compose is well supported by my desperate efforts to create "clean" spreadsheets. Just to let you know boss.

Thx

IJR

 

[JAE]

IJR -
Yes...many times we get a spreadsheet completed only to find it has difficulty with a particular situation in practice.

One thing about writing them, though, as you get "older" and new codes/specifications seem to come out at an amazingly fast rate, updating your spreasheets to the new codes is a great way to keep up.

 

[IJR]

JAE,

I agree with that one too. Once in a while I go back to my few spreadsheets and check them out. For this reason when I write them I usually use "sub-sheets" to do blocks of separate jobs. I mean I would first create a sheet say sheet 17, name it "type of Section", and in that sheet I would only carry out calculations for one block of my final design, say determine Classification of a steel profile as in AISC code. There will be input in red(here geometry of the steel profile) and outputs in blue(whatever summarizes the classification). I would test this one thoroughly, and when satisfied I would paste the whole sheet in my "Main" sheet somewhere in distant columns not readily visible on usual screen, and I would usually name those distant cells for quick acess(I hate scrolling around).This way I have a sort of a "one-time-to-use sub" ready. I would send inputs there and get my output back by only referring to the input and output cells which I dont usually name, doing so very relaxed. To do changes to this sub, all I have to do is go to the hidden zones and play with the body, not the inputs or outputs. Takes time but gets me a well tested final sheet. When suspicious of an output somewhere, no scrolling, all I have to do is study the one "bad" sub.

Yeah, I do love programming but I also seem to share your hidden philosophy(my guess)-A tool is what you make of it. A good tool is a good tool only for you. Programming or a way around an abacus, both can be fine and fast depending on the problem at hand.

Thx JAE

IJR

 

[JAE]

IJR

Amazing how engineers think alike....we use red for input, too!

yakpol

I'm checking about posting our templates - kind of concerned about liability, copywrite, etc.....

What is the structuralist.net site?

 

[yakpol]

JAE,

http://structuralist.net/

is another structural engineering forum from California (established last fall), it does not have as many members and almost no interaction between them, but you can post software and publish there if you wish. The founder, Dennis S. Wish, dedicated a lot of his energy for forum to succeed. By the way, he has a great disclaimer form for the spreadsheets. Even commercial engineering software has disclaimers and not responsible for anything! I copywright the spreadsheets on my site

http://yakpol.net/

protecting myself if somebody will try to sell my work (good luck!). As creating spreadsheets is quite enjoyable I just want to see not just a cirlce of chosen colleagues, but more people using them.
Yakpol