Want to get Array formula with Indirect to work

[TomCon]

The following array formula works correctly:
=MAX((INDIRECT("R3"&"C66:R2533C66",FALSE)=BN4)*($BR$3:$BR$2533))-BR4

But, if i try to use the row() function, it gives #VALUE:
=MAX((INDIRECT("R"&ROW(BS3)&"C66:R2533C66",FALSE)=BN4)*($BR$3:$BR$2533))-BR4

Of course ROW(BS3) evaluates to "3", so, it might seem that the second would also work.

Is there any way to get this to work, to be able to use the row() function in the array formula, or can array formulas simply not be used in this way?

Thanks!
Tom

 

[ukmikeb]

Hi

What version of Excel are you using?

The formula compiles correctly for me in 2003 and 2007.

hth

 

[Jonmo1]

Try

=MAX((INDIRECT("R"&ROWS(A$1:A3)&"C66:R2533C66",FALSE)=BN4)*($BR$3:$BR$2533))-BR4

Not exactly sure why ROWS works, but ROW does not..

 

[TomCon]

Thanks for the Help/comments! I am using Excel 2010, so maybe they have a new bug or regression, if this worked before. For me, use of ROWS() will not solve my underlying problem; i must somewhere use ROW(). I tried some more combinations of solutions. If i put the =ROW() formula in a worksheet cell, i can then concatenate that cell into the INDIRECT() that is within the array formula. That can work for me, tho messy to have to leave those cells around on the sheet just to be referenced; would have prefered to compact it into the array formula. Thought "maybe a defined name would work" but once again, this fails in an array formula if the defined name contains ROW() within it. Even having the defined name be TEXT(ROW(...)) fails. So, only putting an expression that contains ROW() in a different cell seeems to work, from what i can see so far.

Thanks!
Tom

 

[Jonmo1]

For me, use of ROWS() will not solve my underlying problem; i must somewhere use ROW().

Why?

If it can be done with ROW, it can also be done with ROWS.
If anything, ROWS is better, because it will be robust against Row Insertion/Deletion.

 

[T. Valko]

The following array formula works correctly:
=MAX((INDIRECT("R3"&"C66:R2533C66",FALSE)=BN4)*($BR$3:$BR$2533))-BR4

But, if i try to use the row() function, it gives #VALUE:
=MAX((INDIRECT("R"&ROW(BS3)&"C66:R2533C66",FALSE)=BN4)*($BR$3:$BR$2533))-BR4

Of course ROW(BS3) evaluates to "3", so, it might seem that the second would also work.

Is there any way to get this to work, to be able to use the row() function in the array formula, or can array formulas simply not be used in this way?

Thanks!
Tom

Why are you using cell reference BS3?

Try it like this:

...MAX(ROW(BS3))...