×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Auto-Updating Template

Auto-Updating Template

Auto-Updating Template

(OP)
So this is a stretch and I'd be shocked if there's a way to do it...but I have to ask.

Is there a way to make a template in Excel with 'daughter' files that will automatically update if the template is changed?

For instance: I just wrote a fairly simple excel sheet with a number of variables. I then ran that spreadsheet for several permutations of the variables. (Material, length, loading direction, etc.). Well, after running about 100 permutations, I discovered an error in the underlying formulas. Quite upsetting. I'd love it if there were a way to have a 'master' template file where I can fix that one mistake, and the 100 'daughter' files will automatically update and run when I open them.

Pipe dream, I'm sure, but does anyone know of an effective way to accomplish it? Thanks.

RE: Auto-Updating Template

assuming the cells containing the formulas didn't change you could write a quick VBA macro to overwrite the formulas in those specific cells. You could get fancy with it and have the VBA script push to all files in a directory but that has some headaches or at least did when I tried it a decade ago.

shadeshappyGet on the python train with Jupyter Notebook, update the base method file referenced in the notebook and all the notebooks would pull the latest version on run.

I'm making a thing: www.thestructuraltoolbox.com
(It's no Kootware and it will probably break but it's alive!)

RE: Auto-Updating Template

(OP)

Quote (Celt83)

Get on the python train with Jupyter Notebook, update the base method file referenced in the notebook and all the notebooks would pull the latest version on run.

I'd love to, but that's one more thing to learn. And since I'm quasi-actively trying to learn C#, I'd probably get them confused somehow.

I have an idea using the Indirect function. If I create a base file has the formulas as text strings with no leading =, then I think I could create a template that has a bunch of =INDIRECT(<Base file cell reference>) all over it. It's a bit cumbersome and probably not very efficient from a computational point of view, but I could use a VBA script to open the base file in the background to have access to the contents to run the template and get the job done.

RE: Auto-Updating Template

Quote (phamENG)

I'd love to, but that's one more thing to learn. And since I'm quasi-actively trying to learn C#, I'd probably get them confused somehow.
C#, fun.

Indirect will probably get it done but feels like a huge pain in the butt. With the number of permutations is sounds like your doing any reason not to do them all in the same sheet? Had a buddy in college that would actually use Access for this sort of thing the calculated fields were part of the database model and he just added data for the inputs.

I'm making a thing: www.thestructuraltoolbox.com
(It's no Kootware and it will probably break but it's alive!)

RE: Auto-Updating Template

I'm sure it is possible, but I can't think of a fast and efficient way to do it. Some alternatives that come to mind are:

1. Set up a master sheet with VBA code to save the input data for each permutation as a new worksheet, and to re-import the data automatically when required.
2. Use VBA code to create user defined functions for all formulas in the spreadsheet. That file can then be saved as an add-in and the functions can be called from new files for each permutation, so if any of the VBA is corrected all the new files will automatically use the corrected functions.
3. Much as above, but use Python + an Excel add-in package (pyxll and XLwings are the ones I'm familiar with), where all the code is automatically stored seperately to the spreadsheet.

I guess you might not be keen on Option 3, but I have found using Python as an intermediary an effective way to connect Excel to compiled code, so it might be worth thinking about.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Auto-Updating Template

(OP)
Celt - interesting idea. I was a database administrator in a past life, and I used to be decent with SQL...been a while since I messed around with Access in a meaningful way. I'll have to think about how I'd implement that.

IDS - thanks. #2 sounds like the closest thing to what I'm looking for. #3 would be good, but until I learn Python it won't be much use. One day...

RE: Auto-Updating Template

Don't forget the power of structured tables, where one change in a formula changes all the formulae in the column

RE: Auto-Updating Template

I second IDS's #2 approach. Consider moving the calculations to a VBA user defined function located in an addin, then you're just referencing this function from your 100 sheets, you can change the VBA behind the calculations since it is separate to but used by the sheets. Doesn't help with your current predicament, but in future.

Th eonly downside is your users need the addin, but this also helps with protecting IP. Had a few ex employees tell me after they left that my spreadsheet didn't work..... sad for you.

I use this with great success in our office, virtually have only a limited amount of sheet specific vba code in the actual workbooks, anything else is in an addin my users have loaded up. Allows for correcting any sheet if any formula in VBA needs to be changed in a 'live' sense. I've had a few things for example I've had to tweak in a calculation, and provided all the inputs\output are the same the end result is any spreadsheet using that function returns the updated calculation result. Also allows users to use these pre-defined functions in their own spreadsheets, rather than them starting from scratch and risk of them making errors along the way (think for example a single User Defined Function that might return the seismic coefficient from all the required inputs, or calculate a bolt shear or tension capacity from some diameter/grade information). It saves people time.

https://engineervsheep.com

RE: Auto-Updating Template

Another option is to just store each permutation in the same sheet using VBA to collect and write all the relevant inputs to the sheet and record answers to another sheet. That way you only have a single sheet with the required formulas and can regenerate all the 100 output permutations using the most up to date formulas (which you can change at any time) from a set of inputs using that calculation sheet. I hate the copy/paste thing creating 100 sheets for the very reason you've found that if there's an error you're in struggle street.

https://engineervsheep.com

RE: Auto-Updating Template

I'm assuming 100 values in a single column.

If that assumption is correct, why not 100 columns on one sheet rather than 100 sheets.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Auto-Updating Template

(OP)
Thanks, Agent666 - that sounds like a good setup. I'm going to start basing as many of my spreadsheet 'calculators' on that model as I can.

SkipVought - not quite. While I could probably arrange it that way, I need the output to look good for formal submitals. This particular instance was a strange case - I wrote up a calculation tool to run a certain multi-step analysis and design. It's one that I can use over and over again for future work, but I was trying to put together a load table for a wide range of circumstances all at once - the 100 permutations. So I was producing formal calculations to back up each entry in the table. I knew the pattern I was expecting in the table, so when I compiled the results the first time and saw that the trend didn't go as expected, I knew there was an issue. So I suddenly had to go back and change each worksheet (or, rather, delete all but one, fix that one, and recopy and re-iterate the variable selections). The ship has mostly sailed on that - I'll probably come back around to it first to try to implement IDS and Agent666's recommendations as a test before expanding it to other projects.

Thanks again, everyone.

RE: Auto-Updating Template

Probably late to the party on this one, if it's just formulas in cells you need to change rather than VBA code can you not write new VBA code to overwrite the incorrect cells with the new formula?
Open each Excel sheet, run the VBA code and your sheet will be up to date. Rinse and repeat for each sheet.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close