reference to a formula

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 


0
2/18/2009 1:23:41 AM
excel 39879 articles. 2 followers. Follow

14 Replies
734 Views

Similar Articles

[PageSpeed] 5

On Feb 17, 6:23=A0pm, "Fawn Lagimodiere" <flagimodi...@shaw.ca> wrote:
> Hi I have a workbook that has several formulas and 15 sheets which causes
> the workbook to be very large. =A0Is there a way to reference to a formul=
a
> 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.
0
davesexcel (61)
2/18/2009 1:32:33 AM
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" <flagimodiere@shaw.ca> wrote in message 
news:xqJml.8153$FI5.2880@newsfe07.iad...
> 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
> 


0
no3581 (99)
2/18/2009 3:43:35 AM
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" <no@email.address> wrote in message 
news:gng05d$de3$1@daniel-new.mch.sbs.de...
> 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" <flagimodiere@shaw.ca> wrote in message 
> news:xqJml.8153$FI5.2880@newsfe07.iad...
>> 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
>>
>
> 


0
2/18/2009 6:36:28 AM
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

"Fawn Lagimodiere" <flagimodiere@shaw.ca> wrote in message 
news:X%Nml.6416$_U5.2740@newsfe20.iad...
> 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" <no@email.address> wrote in message 
> news:gng05d$de3$1@daniel-new.mch.sbs.de...
>> 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" <flagimodiere@shaw.ca> wrote in message 
>> news:xqJml.8153$FI5.2880@newsfe07.iad...
>>> 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
>>>
>>
>>
>
> 


0
no3581 (99)
2/18/2009 8:21:32 AM
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...?

"Joerg Mochikun" wrote:

> 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
> 
> "Fawn Lagimodiere" <flagimodiere@shaw.ca> wrote in message 
> news:X%Nml.6416$_U5.2740@newsfe20.iad...
> > 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" <no@email.address> wrote in message 
> > news:gng05d$de3$1@daniel-new.mch.sbs.de...
> >> 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" <flagimodiere@shaw.ca> wrote in message 
> >> news:xqJml.8153$FI5.2880@newsfe07.iad...
> >>> 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
> >>>
> >>
> >>
> >
> > 
> 
> 
> 
0
moily (55)
2/18/2009 10:31:01 AM
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 :-)

>> 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" <no@email.address> wrote in message
>> news:gng05d$de3$1@daniel-new.mch.sbs.de...
>>> 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.

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.
-- 
Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2 


0
ooops (23)
2/18/2009 3:38:43 PM
<<<"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!
=====================================================

"Joerg Mochikun" <no@email.address> wrote in message 
news:gnggee$qdg$1@daniel-new.mch.sbs.de...
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

"Fawn Lagimodiere" <flagimodiere@shaw.ca> wrote in message
news:X%Nml.6416$_U5.2740@newsfe20.iad...
> 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" <no@email.address> wrote in message
> news:gng05d$de3$1@daniel-new.mch.sbs.de...
>> 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" <flagimodiere@shaw.ca> wrote in message
>> news:xqJml.8153$FI5.2880@newsfe07.iad...
>>> 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
>>>
>>
>>
>
>



0
ragdyer1 (4060)
2/18/2009 4:12:46 PM
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 !
-----------------------------------------------------------------------------------------------


"Alexander Wolff" <ooops@gmx.de> wrote in message 
news:gnha7s$79d$02$1@news.t-online.com...
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 :-)

>> 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" <no@email.address> wrote in message
>> news:gng05d$de3$1@daniel-new.mch.sbs.de...
>>> 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.

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.
-- 
Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2



0
ragdyer1 (4060)
2/18/2009 4:19:52 PM
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



"RagDyeR" <ragdyer@cutoutmsn.com> wrote in message 
news:OjNW%23OekJHA.1928@TK2MSFTNGP03.phx.gbl...
> <<<"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!
> =====================================================
>
> "Joerg Mochikun" <no@email.address> wrote in message
> news:gnggee$qdg$1@daniel-new.mch.sbs.de...
> 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
>
> "Fawn Lagimodiere" <flagimodiere@shaw.ca> wrote in message
> news:X%Nml.6416$_U5.2740@newsfe20.iad...
>> 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" <no@email.address> wrote in message
>> news:gng05d$de3$1@daniel-new.mch.sbs.de...
>>> 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" <flagimodiere@shaw.ca> wrote in message
>>> news:xqJml.8153$FI5.2880@newsfe07.iad...
>>>> 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
>>>>
>>>
>>>
>>
>>
>
>
> 


0
no3581 (99)
2/19/2009 2:00:23 AM
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


"Joerg Mochikun" <no@email.address> wrote in message 
news:gnieg0$sdi$1@daniel-new.mch.sbs.de...
> 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
>
>
>
> "RagDyeR" <ragdyer@cutoutmsn.com> wrote in message 
> news:OjNW%23OekJHA.1928@TK2MSFTNGP03.phx.gbl...
>> <<<"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!
>> =====================================================
>>
>> "Joerg Mochikun" <no@email.address> wrote in message
>> news:gnggee$qdg$1@daniel-new.mch.sbs.de...
>> 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
>>
>> "Fawn Lagimodiere" <flagimodiere@shaw.ca> wrote in message
>> news:X%Nml.6416$_U5.2740@newsfe20.iad...
>>> 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" <no@email.address> wrote in message
>>> news:gng05d$de3$1@daniel-new.mch.sbs.de...
>>>> 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" <flagimodiere@shaw.ca> wrote in message
>>>> news:xqJml.8153$FI5.2880@newsfe07.iad...
>>>>> 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
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>>
>
> 


0
2/19/2009 6:51:15 AM
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.
-- 
Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2 


0
ooops (23)
2/20/2009 6:22:30 PM
Die Tastatur von RagDyeR wurde wie folgt gedr�ckt:
>> Name Insert Define AnyName refersTo =!A1+!B2
>>                         instead of =sheet1!A1+sheet1!B2
>>
>> But: Test whether the Workbook behaves trustfully :-)


> 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).
-- 
Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2 


0
ooops (23)
2/20/2009 6:27:17 PM
So, isn't it easier using the Indirect() function?

Set it ... and forget it!<bg>
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Alexander Wolff" <ooops@gmx.de> wrote in message 
news:gnmss0$ihk$03$1@news.t-online.com...
> Die Tastatur von RagDyeR wurde wie folgt gedr�ckt:
>>> Name Insert Define AnyName refersTo =!A1+!B2
>>>                         instead of =sheet1!A1+sheet1!B2
>>>
>>> But: Test whether the Workbook behaves trustfully :-)
>
>
>> 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).
> -- 
> Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2
> 


0
ragdyer1 (4060)
2/20/2009 10:45:58 PM
> 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.
-- 
Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2 


0
ooops (23)
2/23/2009 4:59:14 PM
Reply:

Similar Artilces:

How do use the if formula
How do I use an IF formula that displays PASSING if the average of the project grades is at or above 36 points and UNSATISFACTORY if that is not the case? if each score is in cells A1 to A4 then use =IF(AVERAGE(A1:A4)>=36,"PASSING ","UNSATISFACTORY ") -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "Fahim" wrote: > How do I use an IF formula that displays PASSING if the average of the > project grades is at or above 36 points and UNSATIS...

Need to reference a block of cells from another sheet.
Dear All, apologies if this is probably a mundane question, but I didn't know what 'search terms' to put in google to return any results.. I've got a Spreadsheet with 12 tabs (sheets), I need part of tab 12 (sheet) to appear on tabs 1-11 (sheets), so that if I change data in tab 12 (sheet), tabs 1-11 (sheets) are changed too.. the cells themselves will never move location, just the data within them.. Is it possible? Regards Paul. On your sheet1 select the cell that should receive the data from Sheet12; Hold down the Shift key and Click on Sheet11 << this shoul...

Creating charts from formula results
Im trying to create a chart in Excel based on the reult of a column or formula's I wrote. The chart just seems to show the formula as its values, can anyone help? I had a list of employee ages and used a formula to indicate their age bracket (e.g. 16-24, 25-30, 31-40 etc). I now want a bar chart that will show how many employee's are in each bracket. Help is much appreciated asap as its for my job. Thanks in advance. -- Boba Fett ------------------------------------------------------------------------ Boba Fett's Profile: http://www.excelforum.com/member.php?action=getinfo...

Copying formulas
Is there an easy way to copy this to about 700 cells? =SUMPRODUCT((A4:A11998=S6)*(B4:B11998=V3)) there has to be an easier way to copy this. Easier than what? Copy and paste? Or drag to fill? If you want exactly the same in all 700 cells, make the cell references in the formula absolute & it will copy without change. [But why would you want to do that?] If you want any or all of the references to change as the rows or copies change, make the relevant parts relative addresses. -- David Biddulph "tim2216" <tim2216@discussions.microsoft.com> wrote in message new...

Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

copy formula #3
I just upgraded to office 2003. When I get a lower version file from someone, and I do a copy and paste, it copies the exact same formula with the $ sign which I cannot see where to get rid of. The calculation box is set to automatic, not manual. How do I resolve this? I didn't have that problem before. Thanks. Hi this has nothing to do with the Excel version. In this case the formulas was entered with absolute references (the $ signs). If you want the references to change you have to delete these $ signs. Either manually or clicking on the reference and hitting F4 to toggl...

refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

Formula Modification
Folks: (IF(ISERROR(MATCH(B1,A:A,0)),"No Match","Match") Column A Column B Column C 25 50 No Match 30 30 Match 26 28 No Match 55 60 I am using the above formula to compare two columns (A and B) for overlapping (common) data then put the result ("Match" OR "No Match") in a third column. So far this formula has work...

What is IVA# in Distribution Reference and Reference?
We did an inventory adjustment and now we get a unknown amount in Debit under the Inventory account and Credit for COGS account in the Transaction Entry Zoom. We're trying to figure out where that amount came from but we couldn't zoom more than what it's currently shown. In both fields, Reference and Distribution Reference, it's shown IVA#######... that is unknown to or setup by us in anywhere, i.e. Audit Trail, Source Document. Does this amount affect the dollar in the accounts mentioned? And can anyone please help to where we could drill down the IVA details?...

Formula to calculate Latest Cost (inventory valuation)
I have a worksheet that contains the daily purchases for 4 products. column B contains the products column D contains the purchase price column K contains the division column J contains the day of the month I need a formula that will pull the latest purchase price by product by division. In other words the last purchase for the month and the associated purchase price. Thanks -- col You can do that with this array* formula: -- Best Regards, Luke M "Curtis" <curtis.eadie@yahoo.ca.(do not spam)> wrote in message news:61160B81-7277-4C97-8...

how do i enter data for a # of years using a formula?
i am working on excel and the book asks that i enter data s=using formulas for specifically the last three years of what i am referencing to. and i have to know how to us the copy command button. can anyone help ...

Need a formula...
I have been given a task to collect info and create several spreadsheets but I have one more formula to create. Here is it... The formula has to callers receiving points for the amount of Western Union sales they have for the month. Let say the entire department AVERAGE (this number will change every month) was 161 sales; then 161 would be the median. If the caller had 171 - 180 sales then he or she gets +1 point. If the caller had 181 - 190 sales the caller gets 2 points, etc.. until the mark of 321-330 in which they get 16 points. So, if I enter the number 255 in the cell then the an...

Summing types with one formula
I am having difficulty writing a formula to sum two different types of data in single formula. I know how to use an "if" statement to create another two columns and add this way, but I would like to use a vlookup, maybe?, or something to total. Below is a simplified version of what I am trying to get two different sums for. I need a total of "DIV"s and a total of "AP"s as well. Thank you for any assistance. Type Amount AP $1,050,100 AP $249,500 AP ...

macro button refers to prev. workbook?
Hi, When I create a macro in Excel, and assign a button to it, it works fine the first time, but when I re-open the workbook for a second time, and try to run the macro again, the macro seems to refer to the previous workbook...For some reason, the macro saves the name of the previous workbook... Sub test() ' Dim name As String Dim adress As String Sheets("sheet1").Select Range("A1").Select name = ActiveSheet.Range("B6").Value adress = ActiveSheet.Range("B4").Value ActiveWorkbook.SaveAs Filename:="C:\WINDOWS\Desktop\Temp...

Date Formula #2
I am trying to write a formula that works off what is in two separate cells. It has several parts and maybe not all of them can be done. It would be great if someone could help me out. Here goes. If A2 & A3 each have a "N" in it then I want cell A5 to show today's date plus 20 weeks. However, if only 1 of the cells A2 or A3 has a "N" in I still want A5 to show today's date plus 20 weeks. Can this also be done? If both A2 & A3 have "Y" in them then I want A5 to freeze with the date last show when 1 of the two cells had a "N" it ...

Fix Broken Cell Formula with VBA
Greetings, I have a cell formula that is damaged each time I run a delete to remove excess rows before a paste & copy. this formula links to a cell that is always deleted. So I am trying to repair this formula at the end of each cycle. Here is the correct formula: =IF(A11="Proximates","Click","") Here is the same formula after the delete: =IF(#REF!="Proximates","Click","") I tried to fix this in the code that does the finale copy & paste with this: Range("B2").Formula = "=IF(A11=...

Formula Problem2
I am trying to raise a formula that gives me a sum of two cells when there is a positive value present and the following appears to satisfy this. =IF(H55<>"",H55+H56,"") The problem is that if the cell H55 is actually empty, I want to sum two different cells in this case H54+H56. Is there any way of achieving this with the OR or some other function? I received some very helpful replies to this enquiry 30th June 2004 but.............. I am getting there :-) Every one of the suggestions worked but only when I deleted the content of cell H55. Cell H55 actually ...

Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references Dave Tools>Options>View. Uncheck "row and column headers". Good idea to spend a few minutes browsing through the various Tools>Options tabs to see what else is available to toggle on/off. Gord Dibben Excel MVP On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <Dave@discussions.microsoft.com> wrote: >How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...

i need halp with a formula
i am trying to make a formula that says "if text in cell A1 is "apple" then add number in cell A3 to cell A2" If Text in cell A1 is Orange then add number in cell A3 to Cell A2 That looks like the same action to me. Where does the result go? If you want it in A2, you need code. -- HTH RP (remove nothere from the email address if mailing direct) "detlghtpd" <detlghtpd@discussions.microsoft.com> wrote in message news:1B8CC41B-D613-41A1-841A-586F4CE137E4@microsoft.com... > i am trying to make a formula that says "if text in cell A1 is "...

Formula #10
Formula in C3 is =IF(E3>D3/12,E3,D3/12). I would like to have the cell (d3 or e3) where the data entered last control the answer in c3. -- Val Put this in C3 instead..... =IF(COUNT(D3:E3)=2,IF(E3>D3/12,E3,D3/12),"") Vaya con Dios, Chuck, CABGx3 "Val" <Val@discussions.microsoft.com> wrote in message news:DEC352A2-AF08-4796-BF77-E5C1F3F02E3A@microsoft.com... > Formula in C3 is =IF(E3>D3/12,E3,D3/12). > > I would like to have the cell (d3 or e3) where the data entered last control > the answer in c3. > -- > Val ...

Missing References
I had a problem today with a missing reference - Microsoft Forms 2 on a colleague's machine at work (Office 2003; VBA 6.5; XP). I've read Chip Pearson's "Missing References in VBA" which is a geat explanation about how to fix problems like this. Oddly the library didn't seem to be flagged as "missing" and was in fact present in the system32 directory but didn't show up in the list of available references. Now to the questions: I'm not clear on whether if I write code in a project that references a library which is not part of the &qu...

Reference
Is it possible to create a reference to another object such as: int & x = i; but using an image list, and referencing different objects depending on a condition? such as : CImageList & imageList; if (thumbnail == 1) imageList = m_ImageListThumb; else imageList = m_ImageListFullSize; Hope this makes sense! Ben You're better off using pointers for this. I believe, depending on the class, that what you're trying to will make a copy of the object instead of referencing it. "Ben Williamson" <oakdaleclose@googlemail.com> wrote in message news:43ee4603...

zeros in cells with formulas
when I enter a formula in a cell it displays a 0, when there is not an answer to the formula. Is there a way to keep the formula but get rid of the zeros any help is greatly apperciated! Thanks, Darrell Two ways, In Tools>Options>View, uncheck the zero values checkbox - that hides them Other way is to trap it, like =IF(formula=0,"",formula) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Darrell" <anonymous@discussions.microsoft.com> wrote in message news:D4B48DFF-...

Question about charatcer in formula
A co worker is taking an Economics class and has an Excel formula tha calculates raising interest rates. The formula provided is (1+r)^t is represntative of column A. She is unsure what ^ is suppsed to mea or do concerning this formula? Any thoughts and answers are greatl appreciated. Thanks Ja -- Message posted from http://www.ExcelForum.com The ^ (caret) is an arithmetic operator which stands for "Exponentiation" (to the power of) Example: 3^2 is the same as 3*3 -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <at>yahoo<dot>com --- "Jay Hanks >...

Macro to Reference Column Next to Current Reference
Hi, I'd like to automate the following procedure using a macro. Please help. 1) i have 2 sheets: Sheet1 & Sheet2 2) In Sheet1, Cells B2,D2,F2 has the formulas "=Sheet2! F2", "=Sheet2!K2", "=Sheet2!P2" correspondingly. 3) Every month when i do my work, i'd have to manually rekey the formulas to reference the subsequent columns, ie, Cell B2,D2,F2 will change from above to "=Sheet2! G2", "=Sheet2!L2", "=Sheet2!Q2" and in the following month, I have to change manually again to "=Sheet2! H2", "=Sheet2!M2&qu...