PDA

View Full Version : Master Formula



jcihar
11-27-2008, 02:33 AM
Hi,

I got stuck with a very simple and straightforward question I was asked:

The goal is to find a way how to create a master worksheet formula in workbook A (wsheet 1) and then to copy it to many others worksheets and workbooks.

If a need in future to edit/change the formulas, then the goal is only to edit the master formula - the other instances should change automatically.

I adviced to create a UDF and to save it either in PERSONAL.XLS or in .XLA file. Then I played with named formulas, which seems to me could be a solution for one workbook.

Do you have an experience with building such type of master formula and its instances through other workbooks?

Tnahk you for suggestions.


Jiri Cihar

Simon Lloyd
11-27-2008, 05:57 AM
Hi,

I got stuck with a very simple and straightforward question I was asked:Well surely its simple to answer then!


The goal is to find a way how to create a master worksheet formula in workbook A (wsheet 1) and then to copy it to many others worksheets and workbooks. the formula would do what?, just reference one workbook?


If a need in future to edit/change the formulas, then the goal is only to edit the master formula - the other instances should change automatically.Regardless of the impact on the results of all the other formulae in all the other books?


I adviced to create a UDF and to save it either in PERSONAL.XLS or in .XLA file. Then I played with named formulas, which seems to me could be a solution for one workbook.If that was your advice then were you working with the person in this thread (http://www.excelforum.com/excel-new-users/662580-creating-a-master-formula.html)?


Do you have an experience with building such type of master formula and its instances through other workbooks?Unfortunately no as most of my time is taken trying to split the atom in a 3rd dimension!

jcihar
11-27-2008, 03:13 PM
Hi Simon,

thanks for pointing me to the similar question on another board - I then tried crawling through non english conferences and by help of russia SQL forum

I found the idea I can use - Bob Umlas' GLOBAL name definition



There is a limitation, that formulas are not automatically calculated after value changes - but I can to circumvent it by adding an expression +0*NOW() to each defined formula.


Just the minor final question...
I do not understand, why you had noticed me about crossposting - I am not aware I commited it - just pressed the button New Post :wot

Jiri Cihar

Simon Lloyd
11-28-2008, 04:33 AM
Just the minor final question...
I do not understand, why you had noticed me about crossposting - I am not aware I commited it - just pressed the button New Post :wot

Jiri CiharI did not, that is part of my signature statement.