Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming a Formula, using Link to Cell containing that Formula
I know that it is possible to Name a formula, such that placing that Name in
a given location will cause that formula to run relative to that cell. Obviously, this is nice if one wishes to use the same formula throughout a workbook/worksheet, while retaining the ability to change the formula in only one place, while having that change apply throughout the workbook/worksheet. My problem is that the formula that I will be using is both long and complex. Thus, I want to be able to debug the formula in a cell, then (rather than re-typing the formula into the "Refers to" portion of the Name dialog) just reference that "debugged cell" in the "Refers to" section of the Naming Dialog. Can this be done? Alternatively, is there a way to copy the formula into the "Refers to" area, rather than re-typing the whole thing? I've not been able to successfully copy anything into that area. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming a Formula, using Link to Cell containing that Formula
Lets try setting it up by copying your long, edited formula and assigning it
to a Name rather than trying to get even fancier. Step 1: set up the formula somewhere. Be sure you set it up so that when you move it to other cells it works the way you expect in those other locations also. Step 2: With step 1 complete, select the cell with the formula in it and select the entire formula as it is displayed in the Formula Bar (above the worksheet area itself). Use [Ctrl]+[C] or Edit | Copy to copy the formula to the clipboard. Press the [Esc] key. Step 3: use Insert | Name and type in a name for the formula, then in the Refers To area, highlight whatever Excel put in there by default and press [Ctrl]+[V] to paste the formula into it. That should do it for you. Now you can refer to the formula by its name. take a close look at it later - it may pick up the sheet name from the original sheet where you developed the formula and make that part of the Refers To "on the sly" so to speak. --- If you know a little about VBA programming, you can also create what are known as User Defined Functions (UDF) that can perform complex operations and calculations and return the results to the worksheet by using their name just like you do the built-in worksheet functions. Here's a simple example: (system acting up, forgive the repeated post if that happens - JLL) "Lee4" wrote: I know that it is possible to Name a formula, such that placing that Name in a given location will cause that formula to run relative to that cell. Obviously, this is nice if one wishes to use the same formula throughout a workbook/worksheet, while retaining the ability to change the formula in only one place, while having that change apply throughout the workbook/worksheet. My problem is that the formula that I will be using is both long and complex. Thus, I want to be able to debug the formula in a cell, then (rather than re-typing the formula into the "Refers to" portion of the Name dialog) just reference that "debugged cell" in the "Refers to" section of the Naming Dialog. Can this be done? Alternatively, is there a way to copy the formula into the "Refers to" area, rather than re-typing the whole thing? I've not been able to successfully copy anything into that area. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for naming tabs | Excel Discussion (Misc queries) | |||
naming an answer in a formula | Excel Discussion (Misc queries) | |||
EXCEL LINK CELL FORMULA | Excel Worksheet Functions | |||
formula and cell link movements | Excel Worksheet Functions | |||
naming formula | Excel Worksheet Functions |