Repeating Fomulas in Excel

[moo]

Hi,

I have numerous sheets with data in for stats.

So for example I have Customer 1, Customer 2 and Customer 3 sheets each with stats for each month. Each sheet uses the same formula in order to compare the monthly stats. So what I am asking is that because the formula is repeated can I just put the formula on a master spreadsheet which each sub-sheet can refer to.

E.g if the formula was (A1+A2) then could each sheet refer to this on a master sheet but still use its unique data??

Please do not hesitate to ask me for more info if this is in anyway unclear.

Advanced thanks,

Moo.

 

[SkipVought]

Hi,
I think that what you want is Named formula with relative references. See if this will work for you.

1. Select a cell that you want the formula to reside in.

2. Menu Item - Insert/Name/Define.

3. In the Define Name window...
In the Names in Workbook: Textbox, enter a name that will identify your formula.
In the Refers To: Textbox, replace the contents with the formula that you want, such as =A1*A2

4. Hit Add

5. In every place you want to use this formula, enter the name as =YourFormulaName.

6. Whenever your formula changes, return to Insert/Name/Define, select the appropriate name and edit the formula, being certain to use relative references

Hope this helps. Skip,
metzgsk@voughtaircraft.com

 

[moo]

Hi,

Thanks for replying. Just to test it out I have tried the following:

Created a main sheet with the following

Column A
1231
23
123
123
23123
23123

Columnn B
3
123
123
123
123
123

And I have defined a formula called "Global" as "=A1+A2".

I have another sheet with the following:

Column A
1
2
3
4
5
6

Column B
1
2
3
4
5
6


and Column C all equal "=Global"

The problem I am having is that sheet 2 does not add up the data from its sheet, instead it adds the data from the master sheet. What am I doing wrong?

 

[beowork49]

Try Insert/Name/Define again; click your named formula (Global); it probably looks like this: "=Sheet1!A1+Sheet1!A2"; remove "Sheet1" from both places but leave the "!" so it looks like "=!A1+!A2"; click Add; check your results....

 

[SkipVought]

beowork49
Trouble is that Excel REPLACES what you have entered with the Sheet on which you are working. I have tired that. It works WITHIN a sheet, but not BETWEEN sheets. :-( Skip,
metzgsk@voughtaircraft.com

 

[beowork49]

Hmmm. I tried this several times and see it work so that the formula applies to the sheet in which it is entered. What version are you using? I am in Excel 97 SR2. Unfortunately, it does not pick up changes to the values in A1 and A2, only what they were when the named formula was referred to. With Global defined as "=!$A$1+!$A$2" I can put 3 in A1, 4 in A2, =Global in C6 and get 7 in C6. If I change A1 and A2 both to 8, C6 stays at 7, but if I enter =Global in D12, D12 is 16. Also, you have to anchor the formula (eg, "=!$A$1+!$A$2&quot if you want to plug Global into different cell locations around the sheets. I do not know how to get the named formula to adjust to new values (if it even can). I have to confess I have never used this before. I got =!$A$1+!$A$2 figured out by playing with it, and it works within each of the sheets I try it in, but I cannot get the formula to change with changing values in A1 and A2. Regards...

 

[SkipVought]

Moo et al,

IF you want to make a User Defined Function....

1. Activate the Visual BASIC Editor (VBE) alt+F11

2. Insert a Module

3. In the module type...

Function Global1(Val1, Val2)
   Global1 = Val1 + Val2
End Function

Use it just like a built-in function Skip,
metzgsk@voughtaircraft.com

 

[DaleWatson123321]

Got to add my 2 cents worth... seeing as I examined the suggestions made, and came up with ? ? ? ? ? ? ? ? .

Steps:

1) After creating the "Master" sheet, copy or enter the formulas required.

2) For each of the formulas on the Master sheet, create a unique name. Normally, one would use names which can be associated with the type of data. For example, for "percent discount", use a name like "percent_discount". Or you can abbreviate if you prefer.

However, do NOT use names that conflict with Excel's cell coordinates or with numbers. For example, do NOT use "A15" - instead use "A_15" or "_A15". Do NOT use "33" - instead use "_33".

Proper method of Assigning a Range Name.

1) Highlight the cell. i.e. on the Master sheet, place your cursor on a cell containing a formula In the case of a naming a cell containing a formula, you would name only the ONE CELL - as opposed to a range-of-cells). However, one could create a Range Name for a range-of-cells to which a formula refers. In THIS case, however, I believe it's one where you should be assigning the range names to the individual cells - i.e. ONE (unique) name for EACH formula (to which your Customer sheets will refer).

2) Use <Control> <F3> - hold down <Control> and hit <F3>

3) Type a unique name

4) Hit <Enter>

Having named all of the formulas to which you will be referring from your Customer sheets, you can now go to the Customer sheets, and reference your unique range names.

For all formulas that have a unique reference to a cell within the Customer sheet, you can use <Control> A to select the entire sheet, followed by <Control> H to open the &quot;Replace&quot; window. Then under &quot;Find what:&quot;, type an existing cell reference (i.e. currently pointing to the Customer sheet), and then under &quot;Replace with:&quot; enter the range name for the corresponding formula you previously set up and named on your Master sheet. You should now be able to choose &quot;Replace all&quot;. Likely ALL references to your Customer-based formula can be changed to your Master sheet. If in doubt, you could choose &quot;Replace&quot;, and go through the sheet &quot;one formula at a time&quot;.

On the question of replacing ALL of formulas on ALL of the sheet in ONE step, this IS possible, as follows:

1) Click the FIRST sheet &quot;tab&quot; (e.g. &quot;Customer 1&quot

2) Hold down the &quot;SHIFT&quot; key, and

3) Click the LAST sheet &quot;tab&quot; (e.g. Customer 100&quot.

Now proceed... i.e. use <Control> H ...to Replace...

Note: The method of &quot;disengaging&quot; this &quot;group sheet mode&quot;, is to click on a sheet which is NOT &quot;grouped&quot; - i.e. the &quot;Master&quot; sheet.

I sure hope this helps.

Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[beowork49]

Moo (and Skip):
Skip's VBA solution is the better approach. At times, we can get too caught up in Excel's sometimes quirky behavior and forget the power of the macros and functions we can build for ourselves. It might be possible to make the named function work the way you need it to, but it is also not likely to be worth the trouble. The VBA code Skip suggested has the elegance of simplicity. Thanks, Skip...