Template Formula

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?

regards,

thomas

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?

JAC
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

Celia

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.

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.

thomas

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.

HTH

Ivan

Yeah!

Ivan,

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!