Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about repeated usage of a formula
Here is my question (BTW, I am using Excel 2003):
I have a table of values, and I have given each column in the table a name (created with Insert|Name|Define). For instance, these names may be something like Price | Weight | Volume | Age | etc., beginning at address A10 100 | 3 | 10 | 6 | 20 | 5 | 7 | 1 | Now, I want to create a new column with values derived from the values in this table. The difficulty is that I would like to have the formula (for instance, =Weight/Volume) in one place only (say, cell B1), in such as way that I can change the formula in B1 and cause the change to propagate to the entire new column of my table. In essence, what I would like is a function that says: evaluate the formula in $B$1 as if that formula had been written in the current cell. It's something like =offset($b$1,0,0) or =indirect("$B$1"), but for formulas involving Names. Any suggestions would be greatly appreciated. Thanks, Gabriel |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about repeated usage of a formula
You need the formula to be =B10/C10 without names
You will have to type this rather that build it by pointing since pointing always picks up names Then you can copy it down the column Are you using the name for something else? If not then Insert | Names | Define | Delete best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "G.R. Toro" wrote in message m... Here is my question (BTW, I am using Excel 2003): I have a table of values, and I have given each column in the table a name (created with Insert|Name|Define). For instance, these names may be something like Price | Weight | Volume | Age | etc., beginning at address A10 100 | 3 | 10 | 6 | 20 | 5 | 7 | 1 | Now, I want to create a new column with values derived from the values in this table. The difficulty is that I would like to have the formula (for instance, =Weight/Volume) in one place only (say, cell B1), in such as way that I can change the formula in B1 and cause the change to propagate to the entire new column of my table. In essence, what I would like is a function that says: evaluate the formula in $B$1 as if that formula had been written in the current cell. It's something like =offset($b$1,0,0) or =indirect("$B$1"), but for formulas involving Names. Any suggestions would be greatly appreciated. Thanks, Gabriel |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about repeated usage of a formula
Bernard,
Thank you. I was hoping for a more elegant answer. I am trying to avoid having to copy anything (I'll try to do it behind the user's back with vba and a worksheet event). Also, the use of Names makes the process more user friendly. Thanks, Gabriel "Bernard Liengme" wrote in message ... You need the formula to be =B10/C10 without names You will have to type this rather that build it by pointing since pointing always picks up names Then you can copy it down the column Are you using the name for something else? If not then Insert | Names | Define | Delete best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "G.R. Toro" wrote in message m... Here is my question (BTW, I am using Excel 2003): I have a table of values, and I have given each column in the table a name (created with Insert|Name|Define). For instance, these names may be something like Price | Weight | Volume | Age | etc., beginning at address A10 100 | 3 | 10 | 6 | 20 | 5 | 7 | 1 | Now, I want to create a new column with values derived from the values in this table. The difficulty is that I would like to have the formula (for instance, =Weight/Volume) in one place only (say, cell B1), in such as way that I can change the formula in B1 and cause the change to propagate to the entire new column of my table. In essence, what I would like is a function that says: evaluate the formula in $B$1 as if that formula had been written in the current cell. It's something like =offset($b$1,0,0) or =indirect("$B$1"), but for formulas involving Names. Any suggestions would be greatly appreciated. Thanks, Gabriel |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about repeated usage of a formula
Assuming that Weight is something like $B$11:$B$1000 and Volume
$C$11:$C$1000 then in E10 put =Weight/Volume and copy down. This is using Implicit Intersection: Excel uses the current row from Weight and Volume If you want to do the same thing but on different rows for example starting at E1 then select E1:E990 and enter =Weight/Volume using Control/Shift/Enter so that you are creating a multi-row array formula. regards Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "G.R. Toro" wrote in message m... Here is my question (BTW, I am using Excel 2003): I have a table of values, and I have given each column in the table a name (created with Insert|Name|Define). For instance, these names may be something like Price | Weight | Volume | Age | etc., beginning at address A10 100 | 3 | 10 | 6 | 20 | 5 | 7 | 1 | Now, I want to create a new column with values derived from the values in this table. The difficulty is that I would like to have the formula (for instance, =Weight/Volume) in one place only (say, cell B1), in such as way that I can change the formula in B1 and cause the change to propagate to the entire new column of my table. In essence, what I would like is a function that says: evaluate the formula in $B$1 as if that formula had been written in the current cell. It's something like =offset($b$1,0,0) or =indirect("$B$1"), but for formulas involving Names. Any suggestions would be greatly appreciated. Thanks, Gabriel |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about repeated usage of a formula
Charles,
Thank you. What I am trying to avoid is the manual copying and pasting every time the user changes the formula in B1. Gabriel "Charles Williams" wrote in message ... Assuming that Weight is something like $B$11:$B$1000 and Volume $C$11:$C$1000 then in E10 put =Weight/Volume and copy down. This is using Implicit Intersection: Excel uses the current row from Weight and Volume If you want to do the same thing but on different rows for example starting at E1 then select E1:E990 and enter =Weight/Volume using Control/Shift/Enter so that you are creating a multi-row array formula. regards Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "G.R. Toro" wrote in message m... Here is my question (BTW, I am using Excel 2003): I have a table of values, and I have given each column in the table a name (created with Insert|Name|Define). For instance, these names may be something like Price | Weight | Volume | Age | etc., beginning at address A10 100 | 3 | 10 | 6 | 20 | 5 | 7 | 1 | Now, I want to create a new column with values derived from the values in this table. The difficulty is that I would like to have the formula (for instance, =Weight/Volume) in one place only (say, cell B1), in such as way that I can change the formula in B1 and cause the change to propagate to the entire new column of my table. In essence, what I would like is a function that says: evaluate the formula in $B$1 as if that formula had been written in the current cell. It's something like =offset($b$1,0,0) or =indirect("$B$1"), but for formulas involving Names. Any suggestions would be greatly appreciated. Thanks, Gabriel |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question about repeated usage of a formula
Gabriel,
here are 3 suggestions: 1) the user changes the formula in E11 to Weight/Volume and then doubleclicks the little black square at the bottom right of the cell to automatically fill down 2) Use VBA in the Worksheet Change event to do the copy paste 3) Copy the EVAL function you can find at http://www.decisionmodels.com/calcsecretsh.htm into a general VBA module (Atl-F11 -- Insert--Module) in A1 enter '=Weight/Volume (the formula as a string) in E11 enter =INDEX(EVAL($A$1),ROW()-10) and copy (or fill) down Personally I would use solution 1! Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "G.R. Toro" wrote in message m... Charles, Thank you. What I am trying to avoid is the manual copying and pasting every time the user changes the formula in B1. Gabriel "Charles Williams" wrote in message ... Assuming that Weight is something like $B$11:$B$1000 and Volume $C$11:$C$1000 then in E10 put =Weight/Volume and copy down. This is using Implicit Intersection: Excel uses the current row from Weight and Volume If you want to do the same thing but on different rows for example starting at E1 then select E1:E990 and enter =Weight/Volume using Control/Shift/Enter so that you are creating a multi-row array formula. regards Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "G.R. Toro" wrote in message m... Here is my question (BTW, I am using Excel 2003): I have a table of values, and I have given each column in the table a name (created with Insert|Name|Define). For instance, these names may be something like Price | Weight | Volume | Age | etc., beginning at address A10 100 | 3 | 10 | 6 | 20 | 5 | 7 | 1 | Now, I want to create a new column with values derived from the values in this table. The difficulty is that I would like to have the formula (for instance, =Weight/Volume) in one place only (say, cell B1), in such as way that I can change the formula in B1 and cause the change to propagate to the entire new column of my table. In essence, what I would like is a function that says: evaluate the formula in $B$1 as if that formula had been written in the current cell. It's something like =offset($b$1,0,0) or =indirect("$B$1"), but for formulas involving Names. Any suggestions would be greatly appreciated. Thanks, Gabriel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for kanban cycle usage | Excel Discussion (Misc queries) | |||
range usage question | Excel Worksheet Functions | |||
Week Day formula usage???? | Excel Discussion (Misc queries) | |||
Odd excel usage question: | Excel Worksheet Functions | |||
Printing text in a repeated cell/row that is longer than repeated | Excel Discussion (Misc queries) |