CODEWITHSUNDEEP
excelexcel-formula
Dan Dascalescu
Dan Dascalescu

Reputation: 152046

Call formula from cell referenced by name

I've created a relatively complicated formula that sums up a range defined by a start and the end:

=SUMIFS(INDIRECT("Purchases!$B"&INDIRECT("B"&ROW())):INDIRECT("Purchases!$B"&INDIRECT("C"&ROW())), INDIRECT("Purchases!$I"&INDIRECT("B"&ROW())):INDIRECT("Purchases!$I"&INDIRECT("C"&ROW())), "income")

I've defined the formula so that if I simply copy/paste it across other cells, it works perfectly.

Now if I define a name for the first cell where this complicated formula occurs, how can I refer in the other cells to this formula by name, without copy/pasting the formula?

I want some sort of INDIRECT that would execute the formula referenced by its expression; something like "=CALL(sumifrange)", where sumifrange is the name of the cell with the complicated formula.

UPDATE: Here's a macro-enabled spreadsheet that attempts chthonicdaemon's solution. It doesn't work - there's a #REF! error.

Evaluate Excel formula as string

Upvotes: 2

Views: 155

Answers (2)

user4039065
user4039065

Reputation:

Your formula can be made non-volatile by exchanging the INDIRECT function's for the INDEX function and using the principal of Inferred Position (see my explanation here) for the boundary row numbers pulled from columns B and C.

=SUMIFS(INDEX(Purchases!$B:$B,$B:$B ):INDEX(Purchases!$B:$B,$C:$C ), INDEX(Purchases!$I:$I,$B:$B  ):INDEX(Purchases!$I:$I,$C:$C), "income")

Using that Inferred Position, you should be able to set a pair of INDEX functions that will return any range you want depending on the position of the cell referring to them.

Using Formulas ► Defined Names ► Name Manager, define a named range (e.g. siIncome) that uses the following for the Refers to: 

=SUMIFS(INDEX(Purchases!$B:$B,$B:$B ):INDEX(Purchases!$B:$B,$C:$C ), INDEX(Purchases!$I:$I,$B:$B  ):INDEX(Purchases!$I:$I,$C:$C), "income")

Now anywhere you want the result of the formula (relative to the cell's position) you can use =siIncome instead.

      formula as named range

In the OP's sample workbook, the following formula would go into 'SO 29997947'!H2,

=SUMIFS(INDEX('SO 29997947'!$B:$B,$F:$F ):INDEX('SO 29997947'!$B:$B,$G:$G ), INDEX('SO 29997947'!$D:$D,$F:$F  ):INDEX('SO 29997947'!$D:$D,$G:$G), "income")

The Refers to: for sumcateg would be the same formula.

      remote formula

This is used in 'SO 29997947'!I2

Here is the workbook with working revisions. call-formula-by-cell-name.xlsx

Upvotes: 1

chthonicdaemon
chthonicdaemon

Reputation: 19770

You can't do it exactly the way you describe it in Excel anymore. You used to be able to use the EVALUATE function to do roughly what you describe, except you would need your formula as a string. EVALUATE is no longer available to be called as a cell, but you can call it when you define a named range.

Procedure:

  1. Create a cell which contains your formula as a string (so don't start your formula with an equals sign, just SUMIFS(INDIRECT(...

  2. Create a name (let's say func) which has =EVALUATE($A$1) in its "Refers to" box (use absolute references and select the cell where you placed your formula)

  3. In any cell where you need the formula to be evaluated, type =func

Upvotes: 1

Related Questions