I am trying to use pre-specified formulas in excel that can simply be referenced and used anywhere in the workbook, without using VBA. The following example illustrates how I wish this to work.

In the following example, the Forumla Specs section lists various functions we would like to use elsewhere in the workbook

The Formula Use section shows how we would like to use them. How would I call the function in the specs area to calculate the calc1 column (and calc2, calc3, etc.) across the variables d1-d4 using the formula specified in Formula Specs for calc1? I would like to use a generic formula that we can use universally to only depend on the column heading (ie calc1, calc2, etc)?

We have tried using range names, index function, vlookups and various combinations of these to try and achieve this fete, but with no success.

Example

Formula Specs

Calculation Function
calc1       sum(d1,d2,d3,d4)
calc2       sum(d2,d3)
calc3       100*(d4/d3)

Formula Use

    obs     d1  d2  d3  d4  calc1   calc2   calc3
    obs1    24  45  14  41  124     59      292.8571429 
    obs2    19  28  47  34  128         
    obs3    29  32  20  24  105         
    obs4    40  43  28  12  123         
    obs5    39  16  11  40  106         
    obs6    17  14  38  14  83          
    obs7    47  38  26  24  135         
    obs8    31  25  46  15  117         
    obs9    25  15  48  11  99          
    obs10   30  35  32  30  127         
    obs11   41  43  18  15  117         
    obs12   10  34  30  47  121         
    obs13   44  23  10  22  99          

Please help!

---

This is a duplicate of Getting formula of another cell in target cell

Short answer is that you can't without VBA. Also, I don't know why you are so adament about not using UDFs as they are not very complicated to learn about if you are not familiar with VBA.

---

You could use Named Ranges to achieve this:

Select the first cell you want calc1 to be used in

Create a new named range called calc1

Set its Refers To value to =sum(d1,d2,d3,d4)

Put the formula =calc1 into the cell

Now, when you enter the formula =calc1 into another cell, it will sum cells relative to the new cell.

If you don't want it to be relative, use absolute references in the named range, eg =Sum($D1,$D2)

Tried and tested on Excel 2010

链接地址: http://www.djcxy.com/p/60782.html

上一篇: 使用来自访问的公式自动化Excel列

下一篇: Excel中其他地方指定的公式