Auto-Updating Template

[phamENG]

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.

 

[Celt83]

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.

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'm making a thing:

http://www.thestructuraltoolbox.com

(It's no Kootware and it will probably break but it's alive!)

 

[phamENG]

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.

 

[Celt83]

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:

http://www.thestructuraltoolbox.com

(It's no Kootware and it will probably break but it's alive!)

 

[IDS]

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/

 

[phamENG]

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

 

[IFRs]

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

 

[Agent666]

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

 

[Agent666]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[phamENG]

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.

 

[Tuckabag]

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.