#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Reusing formula

I know you can "reuse" a formula by copy/paste to a new cell and the
relative/absolute characteristics of the formula will be preserved. BUt this
means that if the formula needs to change then I have to look for all places
to which it was previously copied and carry out the copy/paste again -
tedious and error prone.

I want to enter a formula once only (eg. in the column heading above where
the formula will be used). Somewhere else in the sheet I want to say "use
the formula provided in cell R,C" and adopt the relative and absolute
references inherent in that formula.

For example ... =USE($H$1) would be a nice function

Can this be done?

An extension of this would be to reuse not only the formula of the referred
cell, but to also reuse some other characteristics of the referred cell

For example ... =USE($H$1,"format"&"conditional format")

Can this be done?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 510
Default Reusing formula

Hi

Define a named formula, so that its returned value depends on address of
cell, it is called from. An example:
Create a named formual (InsertNameDefine)
MyFormula=ROW()*Sheet1!$A$1+COLUMN()*Sheet1!$B$1;
Into range A1:B1 on Sheet1, enter some values;
In any sheet, into some cell enter the formula
=MyFormula
Copy the cell with this formula to some range - you get some values;
Now change your named formula definition to
=POWER(ROW(),Sheet1!$A$1)+POWER(COLUMN(),Sheet1!$B $1)
All return values for formula =MyFormula do change at once.

Another option is to write an UDF, but I'm not sure it'll be possible.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Tony29" wrote in message
...
I know you can "reuse" a formula by copy/paste to a new cell and the
relative/absolute characteristics of the formula will be preserved. BUt
this
means that if the formula needs to change then I have to look for all
places
to which it was previously copied and carry out the copy/paste again -
tedious and error prone.

I want to enter a formula once only (eg. in the column heading above where
the formula will be used). Somewhere else in the sheet I want to say "use
the formula provided in cell R,C" and adopt the relative and absolute
references inherent in that formula.

For example ... =USE($H$1) would be a nice function

Can this be done?

An extension of this would be to reuse not only the formula of the
referred
cell, but to also reuse some other characteristics of the referred cell

For example ... =USE($H$1,"format"&"conditional format")

Can this be done?



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Reusing formula

Thank you - this is helpful. I hadn't realised that one can be a bit
cleverer in the definition of a defined name so that it is a value rather
than simply a cell which contains the value - the value being the actual
formula in this case, including relative cell references.

1. Select the cell containing the formula
2. copy the formula (Ctrl-c) from the formula bar
3. Insert/Name/Define...
4. Name the formula, eg. "MyFormula"
5. Paste (Ctrl-V) the formula into "Refers to:"
6. Click Add

7. In additional cells requiring the same formula enter "=MyFormula"

8. When the formula changes due to redesign, simply go to
Insert/Name/Define, select MyFormula and change its value in the "Refers to:"
field

"Arvi Laanemets" wrote:

Hi

Define a named formula, so that its returned value depends on address of
cell, it is called from. An example:
Create a named formual (InsertNameDefine)
MyFormula=ROW()*Sheet1!$A$1+COLUMN()*Sheet1!$B$1;
Into range A1:B1 on Sheet1, enter some values;
In any sheet, into some cell enter the formula
=MyFormula
Copy the cell with this formula to some range - you get some values;
Now change your named formula definition to
=POWER(ROW(),Sheet1!$A$1)+POWER(COLUMN(),Sheet1!$B $1)
All return values for formula =MyFormula do change at once.

Another option is to write an UDF, but I'm not sure it'll be possible.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Tony29" wrote in message
...
I know you can "reuse" a formula by copy/paste to a new cell and the
relative/absolute characteristics of the formula will be preserved. BUt
this
means that if the formula needs to change then I have to look for all
places
to which it was previously copied and carry out the copy/paste again -
tedious and error prone.

I want to enter a formula once only (eg. in the column heading above where
the formula will be used). Somewhere else in the sheet I want to say "use
the formula provided in cell R,C" and adopt the relative and absolute
references inherent in that formula.

For example ... =USE($H$1) would be a nice function

Can this be done?

An extension of this would be to reuse not only the formula of the
referred
cell, but to also reuse some other characteristics of the referred cell

For example ... =USE($H$1,"format"&"conditional format")

Can this be done?




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default Reusing formula

Thanks for that explanation, Never really understood this - was always
concerned about relative references and had never bothered to test it
out. Will save me a bit of work, that's for sure.

Cheers

Tony29 wrote:
Thank you - this is helpful. I hadn't realised that one can be a bit
cleverer in the definition of a defined name so that it is a value rather
than simply a cell which contains the value - the value being the actual
formula in this case, including relative cell references.

1. Select the cell containing the formula
2. copy the formula (Ctrl-c) from the formula bar
3. Insert/Name/Define...
4. Name the formula, eg. "MyFormula"
5. Paste (Ctrl-V) the formula into "Refers to:"
6. Click Add

7. In additional cells requiring the same formula enter "=MyFormula"

8. When the formula changes due to redesign, simply go to
Insert/Name/Define, select MyFormula and change its value in the "Refers to:"
field

"Arvi Laanemets" wrote:

Hi

Define a named formula, so that its returned value depends on address of
cell, it is called from. An example:
Create a named formual (InsertNameDefine)
MyFormula=ROW()*Sheet1!$A$1+COLUMN()*Sheet1!$B$1;
Into range A1:B1 on Sheet1, enter some values;
In any sheet, into some cell enter the formula
=MyFormula
Copy the cell with this formula to some range - you get some values;
Now change your named formula definition to
=POWER(ROW(),Sheet1!$A$1)+POWER(COLUMN(),Sheet1!$B $1)
All return values for formula =MyFormula do change at once.

Another option is to write an UDF, but I'm not sure it'll be possible.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Tony29" wrote in message
...
I know you can "reuse" a formula by copy/paste to a new cell and the
relative/absolute characteristics of the formula will be preserved. BUt
this
means that if the formula needs to change then I have to look for all
places
to which it was previously copied and carry out the copy/paste again -
tedious and error prone.

I want to enter a formula once only (eg. in the column heading above where
the formula will be used). Somewhere else in the sheet I want to say "use
the formula provided in cell R,C" and adopt the relative and absolute
references inherent in that formula.

For example ... =USE($H$1) would be a nice function

Can this be done?

An extension of this would be to reuse not only the formula of the
referred
cell, but to also reuse some other characteristics of the referred cell

For example ... =USE($H$1,"format"&"conditional format")

Can this be done?



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default Reusing formula

Just wondering if there is a way to get this so the named formula can be
used across numerous sheets (without pointing back to the original sheet
where it was entered), or do I have to do one for each?

TIA


Mark K. wrote:
Thanks for that explanation, Never really understood this - was always
concerned about relative references and had never bothered to test it
out. Will save me a bit of work, that's for sure.

Cheers

Tony29 wrote:
Thank you - this is helpful. I hadn't realised that one can be a bit
cleverer in the definition of a defined name so that it is a value
rather than simply a cell which contains the value - the value being
the actual formula in this case, including relative cell references.

1. Select the cell containing the formula
2. copy the formula (Ctrl-c) from the formula bar
3. Insert/Name/Define...
4. Name the formula, eg. "MyFormula"
5. Paste (Ctrl-V) the formula into "Refers to:"
6. Click Add

7. In additional cells requiring the same formula enter "=MyFormula"

8. When the formula changes due to redesign, simply go to
Insert/Name/Define, select MyFormula and change its value in the
"Refers to:" field

"Arvi Laanemets" wrote:

Hi

Define a named formula, so that its returned value depends on address
of cell, it is called from. An example:
Create a named formual (InsertNameDefine)
MyFormula=ROW()*Sheet1!$A$1+COLUMN()*Sheet1!$B$1;
Into range A1:B1 on Sheet1, enter some values;
In any sheet, into some cell enter the formula
=MyFormula
Copy the cell with this formula to some range - you get some values;
Now change your named formula definition to
=POWER(ROW(),Sheet1!$A$1)+POWER(COLUMN(),Sheet1!$B $1)
All return values for formula =MyFormula do change at once.

Another option is to write an UDF, but I'm not sure it'll be possible.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Tony29" wrote in message
...
I know you can "reuse" a formula by copy/paste to a new cell and the
relative/absolute characteristics of the formula will be preserved.
BUt this
means that if the formula needs to change then I have to look for
all places
to which it was previously copied and carry out the copy/paste again -
tedious and error prone.

I want to enter a formula once only (eg. in the column heading above
where
the formula will be used). Somewhere else in the sheet I want to
say "use
the formula provided in cell R,C" and adopt the relative and absolute
references inherent in that formula.

For example ... =USE($H$1) would be a nice function

Can this be done?

An extension of this would be to reuse not only the formula of the
referred
cell, but to also reuse some other characteristics of the referred cell

For example ... =USE($H$1,"format"&"conditional format")

Can this be done?




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Reusing formula

A defined name is global to the .xls ie. across all sheets in the file,
because the definition's value includes the SheetName!. This is verified by
the Define dialog box - "Names in workbook:". In some other sheet you simply
enter into a cell "=MyFormula" and it will behave as if you had copy/pasted
the formula into that cell and relative/absolute references of the source
formula will be preserved

Tony

"Mark K." wrote:

Just wondering if there is a way to get this so the named formula can be
used across numerous sheets (without pointing back to the original sheet
where it was entered), or do I have to do one for each?

TIA


Mark K. wrote:
Thanks for that explanation, Never really understood this - was always
concerned about relative references and had never bothered to test it
out. Will save me a bit of work, that's for sure.

Cheers

Tony29 wrote:
Thank you - this is helpful. I hadn't realised that one can be a bit
cleverer in the definition of a defined name so that it is a value
rather than simply a cell which contains the value - the value being
the actual formula in this case, including relative cell references.

1. Select the cell containing the formula
2. copy the formula (Ctrl-c) from the formula bar
3. Insert/Name/Define...
4. Name the formula, eg. "MyFormula"
5. Paste (Ctrl-V) the formula into "Refers to:"
6. Click Add

7. In additional cells requiring the same formula enter "=MyFormula"

8. When the formula changes due to redesign, simply go to
Insert/Name/Define, select MyFormula and change its value in the
"Refers to:" field

"Arvi Laanemets" wrote:

Hi

Define a named formula, so that its returned value depends on address
of cell, it is called from. An example:
Create a named formual (InsertNameDefine)
MyFormula=ROW()*Sheet1!$A$1+COLUMN()*Sheet1!$B$1;
Into range A1:B1 on Sheet1, enter some values;
In any sheet, into some cell enter the formula
=MyFormula
Copy the cell with this formula to some range - you get some values;
Now change your named formula definition to
=POWER(ROW(),Sheet1!$A$1)+POWER(COLUMN(),Sheet1!$B $1)
All return values for formula =MyFormula do change at once.

Another option is to write an UDF, but I'm not sure it'll be possible.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Tony29" wrote in message
...
I know you can "reuse" a formula by copy/paste to a new cell and the
relative/absolute characteristics of the formula will be preserved.
BUt this
means that if the formula needs to change then I have to look for
all places
to which it was previously copied and carry out the copy/paste again -
tedious and error prone.

I want to enter a formula once only (eg. in the column heading above
where
the formula will be used). Somewhere else in the sheet I want to
say "use
the formula provided in cell R,C" and adopt the relative and absolute
references inherent in that formula.

For example ... =USE($H$1) would be a nice function

Can this be done?

An extension of this would be to reuse not only the formula of the
referred
cell, but to also reuse some other characteristics of the referred cell

For example ... =USE($H$1,"format"&"conditional format")

Can this be done?



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default Reusing formula

Yes, I can use the name anywhere, but the formula always refers back to
the original sheet where the name was defined. So, with C1 selected on
sheet1, I define a name (say "test") and enter =A1*B1. If I enter =test
into a cell anywhere, the relative cells on sheet1 are used, no matter
what sheet I'm on.

In fact, the formula is automatically changed to =Sheet1!A1*Sheet1!B1 .
I would like it not to do that if at all possible.

TIA


Tony29 wrote:
A defined name is global to the .xls ie. across all sheets in the file,
because the definition's value includes the SheetName!. This is verified by
the Define dialog box - "Names in workbook:". In some other sheet you simply
enter into a cell "=MyFormula" and it will behave as if you had copy/pasted
the formula into that cell and relative/absolute references of the source
formula will be preserved

Tony

"Mark K." wrote:

Just wondering if there is a way to get this so the named formula can be
used across numerous sheets (without pointing back to the original sheet
where it was entered), or do I have to do one for each?

TIA


Mark K. wrote:
Thanks for that explanation, Never really understood this - was always
concerned about relative references and had never bothered to test it
out. Will save me a bit of work, that's for sure.

Cheers

Tony29 wrote:
Thank you - this is helpful. I hadn't realised that one can be a bit
cleverer in the definition of a defined name so that it is a value
rather than simply a cell which contains the value - the value being
the actual formula in this case, including relative cell references.

1. Select the cell containing the formula
2. copy the formula (Ctrl-c) from the formula bar
3. Insert/Name/Define...
4. Name the formula, eg. "MyFormula"
5. Paste (Ctrl-V) the formula into "Refers to:"
6. Click Add

7. In additional cells requiring the same formula enter "=MyFormula"

8. When the formula changes due to redesign, simply go to
Insert/Name/Define, select MyFormula and change its value in the
"Refers to:" field

"Arvi Laanemets" wrote:

Hi

Define a named formula, so that its returned value depends on address
of cell, it is called from. An example:
Create a named formual (InsertNameDefine)
MyFormula=ROW()*Sheet1!$A$1+COLUMN()*Sheet1!$B$1;
Into range A1:B1 on Sheet1, enter some values;
In any sheet, into some cell enter the formula
=MyFormula
Copy the cell with this formula to some range - you get some values;
Now change your named formula definition to
=POWER(ROW(),Sheet1!$A$1)+POWER(COLUMN(),Sheet1!$B $1)
All return values for formula =MyFormula do change at once.

Another option is to write an UDF, but I'm not sure it'll be possible.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Tony29" wrote in message
...
I know you can "reuse" a formula by copy/paste to a new cell and the
relative/absolute characteristics of the formula will be preserved.
BUt this
means that if the formula needs to change then I have to look for
all places
to which it was previously copied and carry out the copy/paste again -
tedious and error prone.

I want to enter a formula once only (eg. in the column heading above
where
the formula will be used). Somewhere else in the sheet I want to
say "use
the formula provided in cell R,C" and adopt the relative and absolute
references inherent in that formula.

For example ... =USE($H$1) would be a nice function

Can this be done?

An extension of this would be to reuse not only the formula of the
referred
cell, but to also reuse some other characteristics of the referred cell

For example ... =USE($H$1,"format"&"conditional format")

Can this be done?

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Reusing formula

I get your point and agree. Sorry - no solution - but, hey, my problem is
solved so I'm back to work

Thanks for your contribution.

"Mark K." wrote:

Yes, I can use the name anywhere, but the formula always refers back to
the original sheet where the name was defined. So, with C1 selected on
sheet1, I define a name (say "test") and enter =A1*B1. If I enter =test
into a cell anywhere, the relative cells on sheet1 are used, no matter
what sheet I'm on.

In fact, the formula is automatically changed to =Sheet1!A1*Sheet1!B1 .
I would like it not to do that if at all possible.

TIA


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 06:18 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM
Creating a check mark box MarthaSue Setting up and Configuration of Excel 18 April 28th 05 12:31 AM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM


All times are GMT +1. The time now is 11:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"