reference to a formula

[Fawn Lagimodiere]

Hi I have a workbook that has several formulas and 15 sheets which causes
the workbook to be very large. Is there a way to reference to a formula
instead of copy and pasting the same formula on every worksheet?

thanks

 

[CurlyDave]

Hi I have a workbook that has several formulas and 15 sheets which causes
the workbook to be very large.  Is there a way to reference to a formula
instead of copy and pasting the same formula on every worksheet?

thanks

Group the sheets, select the first sheet the press Shift and select
the last sheet, they are now grouped, or to group different sheets
press Ctrl then various sheets to group them, once grouped , enter the
formula once and all the sheets in the group will have that formula.

 

[Joerg Mochikun]

You can reference a formula, but of course you still have to copy the
reference whereever it is required. Not sure if this will result in a
smaller filesize or better recalculation speed.

Let's assume your formula in A1 is =(B1+C1)/2
Go to Insert|Name|Define
Into "Names in Workbook" add a name for your formula (e.g.myformula)
Into "Refers To:" copy your formula from A1
Press "OK"

Now you can use the formula =myformula everywhere in your workbook.
References are automatically adjusted.

Cheers,

Joerg Mochikun

 

[Fawn Lagimodiere]

Hi

I entered the formula on sheet 1 and then sheet 2 however it does the
calculation only on sheet1. Is there a way to have this name range on all
the sheets, I have 15 sheets with the same calculations

Thanks

 

[Joerg Mochikun]

Sorry, you are right. And no, there seems to be no way to make a named
function available to all sheets (same a named range, which always refers to
a range on a specific sheet).

If you want a formula reference working on any sheet you would have to
create a custom function. E.g. for the previous example you could put the
following function on a macro module:

Function myformula()
myformula = (ActiveCell.Offset(0, 1).Value + ActiveCell.Offset(0,
2).Value) / 2
End Function

In your spreadsheet you would enter =myformula()
However when using custom formulas the (slow) calculation speed is probably
more an issue than file size.

Another approach, quick and saving file size, would be a do the calculations
via a macro and not via formulas.

Sorry again for not being able to help you more.

Joerg

 

[moily]

I may be vastly oversimplifying what you need but:

If you need to have the results of the formula in cell A1 on the first sheet
repeated on all the sheets just link cells A1 in all the other worksheets to
the cell in the first sheet (='first sheet'!A1).

If you are trying to cut down the amount of repetition of repeatedly
entering the same formula in the same cell on all sheets (ie the same formula
on each worksheet will use different data therefore have different results)
use CurlyDave's suggestion: Select all the appropriate sheets, select A1 on
one of the sheets and enter in the formula you wish. That'll copy everything
you do to all of the sheets at the same time.

Otherwise, if you explained more about what you need...?

 

[Alexander Wolff]

Die Tastatur von Joerg Mochikun wurde wie folgt gedrückt:

Sorry, you are right. And no, there seems to be no way to make a named
function available to all sheets (same a named range, which always
refers to a range on a specific sheet).

Name Insert Define AnyName refersTo =!A1+!B2
instead of =sheet1!A1+sheet1!B2

But: Test whether the Workbook behaves trustfully

This does not reduce size of the formulas underlying. A defined name only
shows the formula in another view. Only a VBA function will reduce size of
an UDF.

 

[RagDyeR]

<<<"Sorry, you are right. And no, there seems to be no way to make a named
function available to all sheets">>>

This is not really accurate.

You *can* universally assign a named formula to all WS's in a WB with a
single procedure.
In fact ... any additionally added sheets will also contain this "named
formula".

Say you want the formula,
=A1*B1
to work on each sheet of the WB, referencing the cells on the individual
sheets themselves.

From *any* sheet in the WB,
Create the named formula - from the Menu Bar:
<Insert> <Name> <Define>
In the "Names in Workbook" box, type something short, say:
calc

Then, change whatever's in the "Refers To" box to this:

=Indirect("a1)*Indirect("b1")

And hit <OK>

Now, on *any* sheet, you can enter the formula:
=calc
And it will multiply A1*B1 of the sheet you're in.

You can, of course, also create more complicated formulas using this
procedure:

=SUM(INDIRECT("A1:A5"))
=AVERAGE(INDIRECT("A1:A5"))
=VLOOKUP(INDIRECT("D1"),INDIRECT("B1:C10"),2,0)


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Sorry, you are right. And no, there seems to be no way to make a named
function available to all sheets (same a named range, which always refers to
a range on a specific sheet).

If you want a formula reference working on any sheet you would have to
create a custom function. E.g. for the previous example you could put the
following function on a macro module:

Function myformula()
myformula = (ActiveCell.Offset(0, 1).Value + ActiveCell.Offset(0,
2).Value) / 2
End Function

In your spreadsheet you would enter =myformula()
However when using custom formulas the (slow) calculation speed is probably
more an issue than file size.

Another approach, quick and saving file size, would be a do the calculations
via a macro and not via formulas.

Sorry again for not being able to help you more.

Joerg

 

[RagDyeR]

The procedure you've described *should not* be used!

It will *not automatically* update the formula returns when the data in the
referenced cells is/are revised,

You would have to go into each cell that used that formula and manually edit
the formula to obtain the new results.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


Die Tastatur von Joerg Mochikun wurde wie folgt gedrückt:

Sorry, you are right. And no, there seems to be no way to make a named
function available to all sheets (same a named range, which always
refers to a range on a specific sheet).

Name Insert Define AnyName refersTo =!A1+!B2
instead of =sheet1!A1+sheet1!B2

But: Test whether the Workbook behaves trustfully

This does not reduce size of the formulas underlying. A defined name only
shows the formula in another view. Only a VBA function will reduce size of
an UDF.

 

[Joerg Mochikun]

Thanks for pointing to the sometimes quite helpful INDIRECT function. Though
your approach would make the named formula indeed available on all sheets,
it limits your formula to absolute addresses (and it adds a layer of
complexity). In such case it would be easier to place the formula =A1*B1
into a cell and simply reference the cell containing the formula. This is
not the normal scenario when you have many repeated formulas on a sheet. In
most cases you would have some relative references in the formula, in which
case you would be stuck with a named formula.

Joerg

 

[Fawn Lagimodiere]

thanks to all that replied to my question. There is lots of good advice
here and it may not work on this workbook but maybe I can use it on another

 

[Alexander Wolff]

Die Tastatur von RagDyeR wurde wie folgt gedrückt:

<<<"Sorry, you are right. And no, there seems to be no way to make a
named function available to all sheets">>>

This is not really accurate.

You *can* universally assign a named formula to all WS's in a WB with
a single procedure.
In fact ... any additionally added sheets will also contain this
"named formula".

Say you want the formula,
=A1*B1
to work on each sheet of the WB, referencing the cells on the
individual sheets themselves.

From *any* sheet in the WB,
Create the named formula - from the Menu Bar:
<Insert> <Name> <Define>
In the "Names in Workbook" box, type something short, say:
calc

Then, change whatever's in the "Refers To" box to this:

=Indirect("a1)*Indirect("b1")

And hit <OK>

Now, on *any* sheet, you can enter the formula:
=calc
And it will multiply A1*B1 of the sheet you're in.

You can, of course, also create more complicated formulas using this
procedure:

=SUM(INDIRECT("A1:A5"))
=AVERAGE(INDIRECT("A1:A5"))
=VLOOKUP(INDIRECT("D1"),INDIRECT("B1:C10"),2,0)

Hi Ray, this does not enable you to automatically adjusting references when
copied WITHIN the sheet.

 

[Alexander Wolff]

Die Tastatur von RagDyeR wurde wie folgt gedrückt:

The procedure you've described *should not* be used!

It will *not automatically* update the formula returns when the data
in the referenced cells is/are revised,

You would have to go into each cell that used that formula and
manually edit the formula to obtain the new results.

The reasons to possibly avoid the use of my formula =!A1... are different
from those you mentioned.

For your manual edit problem, you always can automatically "Search for =,
Replace with =" if Ctrl-Alt-F9 does not work (this was not tested by me at
this special question).

 

[RagDyer]

So, isn't it easier using the Indirect() function?

Set it ... and forget it!<bg>

 

[Alexander Wolff]

So, isn't it easier using the Indirect() function?

=INDIRECT does not allow copying relative addresses, as you might desire (in
many cases). This you must trade against its 3D-capability.