MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Template Formula

Posted by Jill-ann aka Jac on May 23, 2000 3:14 PM

I have a workbook in which I will have 10 worksheets. These 10 worksheets will be calculating results for 10 different markets. The FORMULA'S will be identical on each, they will just be pulling different data based their market (a reference to the market number). In the past, I would simply create the first worksheet and then copy it for the other 9 markets. However, with the CONSTANT tweaks that I am asked to make, I would forget to copy changes to all sheets or make some other equally simple mistakes that quickly jeopardized the integrity of the information. Now my question.

Is there a way to write a formula once and then refer to it in other formulas? For example, if the formula is "=sum(b1:b10)*a11", I would like to write that formula once, and then refer to the "XYZ" formula every time I needed to use it.

I'm not versed in visual basic, so if that's the only answer, I would appreciate if you addressed me as if I were 3 years old.

Thanks, in advance!

Posted by thomas venn on May 24, 2000 9:12 AM

Hi Jill,

perhaps you can give a better example of what you need. your example is rather confusing. when you say refer to "XYZ", does "XYZ refer to "=sum(b1:b10)*a11"? and do you want to refer to the result of "=sum(b1:b10)*a11" or do you want to copy the formula?



Posted by Jac on May 24, 2000 9:46 AM

Thanks for the feedback, Thomas.

I want to compose a formula once and then copy the formula several times. However, I want to be able to change the formula once and not have to worry about re-copying it. So, The "XYZ" in my example refers to the formula itself and not the result of the formula.

For example, let's say I have the formula "=sum(b1:b10)*a11" and it's formula for calculating Income Tax. In every instance I want to calculate tax, I would like to simply type =IncomeTax, and it would be the equivalent of copying the formula. Then, in the future if we determine that the formula for Income Tax is really "=(sum(b1:b10)-b4)*a11" then I would only change it once and every cell that refers to IncomeTax would automatically change.

Clear as mud?

Posted by Celia on May 24, 2000 3:18 PM

Perhaps you can do it with INDIRECT().

For example :-

In cell A1 enter the formula =SUM(B1:B10)*A11

In some other cell enter the formula =INDIRECT(“A1”)
This gives the result of the formula in cell A1

Change the formula in cell A1 to =SUM(B1:B10)-B4)*A11
The result in the other cell will change


Posted by Jill-ann on May 24, 2000 4:37 PM

So Close!

I guess I'm not explaining myself very well. Let's look at it like this: I want to create my own FUNCTION. The INDIRECT idea was close, but it only gives me the RESULT of that formula. I want it to give me the FORMULA. Like this (effectively):

B11 says "=sum(b1:b10)*a11". B11 is named Taxes.

On D141 I want to say "=Taxes" and instead of it giving me the result that is in B11, I want it to give me that formula but have the ranges/references in that formula to be relational D141. Let's say I do that 20 more times. Now, if I alter the FORMULA in B11, all the FORMULAS that refer Taxes would change as well.

This is very similar to what the TABLE function does for us.

Posted by thomas venn on May 24, 2000 4:37 PM

Hi Jill,

you have a good question, but i don't have an answer. however, if your worksheet is a template, and the formula is in the same cell for all sheet, you can easily record a macro to change that particular cell in all the sheets.

For example, let's say your formula cell is in A1, this would mean that your formula would have to be in cell A1 for ALL sheets. If this is the case, then you can write a macro to copy and paste.

hopefully this helps.


Posted by Ivan Moala on May 24, 2000 5:51 PM

Re: So Close!

Using your example
To do this you will have to insert a defined name.
Try this;

Click on the formula cell you want to convert
(You must be in this cell for it to be relative)

Goto Insert... select Name / Define
In the Names in workbook Type Taxes.
In the Refers to Dialogbox type in your formula
eg =Sum(B1:B10)*A11 (DO NOT USE $ as you want this to remain relative)
Click on the Add button.

Now in the formula cell (B11) type in =Taxes
You should see the result.

If you data remains the same relative to the
range you specified in the Taxes (ie. B1:B10 A11)
then typing = Taxes in any other cell will give
you the result relative to your Defined range.

So if you want to change the Taxes (would that be great :-) ) then
Change it in the Defined Name setup.
eg Taxes go down by 0.05 then your new Taxes
should be =Sum(B1:B10)*(A11-0.05)

NB: If you do change it make sure you change it in
a cell that has the defined Name Taxes

The only draw back to this is that you must define
it for your other sheets as the defined name will
only apply to the sheet it is defined in.



Posted by Jill-ann on May 25, 2000 8:18 AM



Yet again, you've hit the nail on the head. That is EXACTLY what I need to do. I think I can live with changing it on each sheet.

Are you aware of any way to do this using visual basic that will work on all sheets?

Thanks, so much!