Search code examples
excelexcel-formulaworksheet-function

Different result in Cell and Name Manager with same formula

My formula is:

=SUMPRODUCT(MAX((OFFSET(sheet1!$A$1,ROW(sheet1!dynamic_cell_anchor),0,1,1000)<>"")*COLUMN(OFFSET(sheet1!$A$1,ROW(sheet1!dynamic_cell_anchor),0,1,1000))))

which can directly return the right result(the largest non-blank column number in that range) in CELL.

BUT when this same formula was firstly defined in Name Manager then be loaded by its name in CELL, it would show as #VALUE! in CELL.

I checked both by Evaluate Formula, They show different middle step:

Directly in CELL,

SUMPRODUCT(MAX((OFFSET(sheet1!$A$1,30,0,1,1000)<>"")*COLUMN(OFFSET(sheet1!$A$1,ROW(sheet1!dynamic_cell_anchor),0,1,1000))))

Defined in Name Manager firstly,

SUMPRODUCT(MAX((OFFSET(sheet1!$A$1,{30},0,1,1000)<>"")*COLUMN(OFFSET(sheet1!$A$1,ROW(sheet1!dynamic_cell_anchor),0,1,1000))))

Obviously, a pair of curly brackets affects the result.

Buy why? and How can this be solved? (I prefer to define it in Name Manager.)

Solution

  • Try wrapping ROW(sheet1!dynamic_cell_anchor) in SUM i.e. 

    SUM(ROW(sheet1!dynamic_cell_anchor))