Naming a Formula, using Link to Cell containing that Formula

[Lee4]

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.

 

[JLatham]

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)