1

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 (i.e 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!

Aleks Danger
  • 93
  • 7
  • Sounds like delegates for excel. Never seen something like that. – Daniel Möller May 17 '13 at 02:39
  • 1
    Why no VBA? That's exactly what it's for. – nnnnnn May 17 '13 at 02:41
  • have to agree with @nnnnnn this seems to be a textbook example of a "User-Defined Function". Otherwise, neither of these formula really seem difficult enough to warrant a custom function. I would just copy/paste. – David Zemens May 17 '13 at 03:16
  • EVAL() from Excel 4 seems to do the trick: http://newtonexcelbach.wordpress.com/2010/12/14/more-on-the-excel-evaluate-function/ – Jüri Ruut May 17 '13 at 05:57

2 Answers2

1

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.

Community
  • 1
  • 1
ApplePie
  • 8,814
  • 5
  • 39
  • 60
  • Thanks for the prompt response. The reason why I have chosen to avoid VBA (if possible) is because the solution I am working to build needs to be as simple as possible to use by our clients. On that note, I realise a UDF may still achieve this. For instance, _=derive(cellref)_ could take the heading (i.e. calc1, calc2, etc.) from the data table, look it up in the specification sheet, read the instructions, and apply them across the data rows (obs1, obs2, etc.) Is it safe to request this on StackOverflow considering I haven't first attempted to produce this myself? – Aleks Danger May 18 '13 at 07:01
0

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

chris neilsen
  • 52,446
  • 10
  • 84
  • 123