Problem with Indirect function in an Array Formula

[gassarma]

Hi, i am trying use Indirect function in an array formula.
="{="&INDIRECT("J1")&"(IF((Sheet 1'!$B$2:$B$3761=$A9)*('Sheet 1'!$H$2:$H$3761=1)*('Sheet 1'!$J$2:$J$3761=1),'Sheet 1'!M$2:M$3761))}"
Where the cell J1 contains a cell dropdown with options such as product, average, min, max and the user can determine whether he wants an average or a product of the array forumla result. It is resulting in only the text. Can anyone please help.

Thanks

 

[gassarma]

One way to do this is use the old Excel4 macro EVALUATE

Define a named range called FlexFormula, using this in the "refers to" box

=EVALUATE('Sheet 1'!$J$1&"(IF(('Sheet 1'!$B$2:$B$3761=$A9)*('Sheet 1'!$H$2:$H$3761=1)*('Sheet 1'!$J$2:$J$3761=1),'Sheet 1'!$M$2:$M$3761))")

Somewhere in the worksheet use this formula

=FlexFormula

and change J1 to the required function

[Note: when I tested the result updated if I changed the function in J1 but if I changed the data in the ranges it didn't update unless I re-entered the formula - I'm using Excel 2007, I don't know if it's peculiar to that version......]

Interesting idea. Thanks. But the challenge is I have to use this formula and copy it across a range of 30 Columns (for different years - columns M onwards) and 20 rows(different values in Cloumn A). Would it still work?

 

[barry houdini]

...Would it still work?..

mmm....that would certainly make it trickier, you might have to use VBA as Glenn suggested - which version of Excel are you using?

 

[gassarma]

Alternately, i am trying to use teh following formula..


=subtotal(6,IF((Sheet 1'!$B$2:$B$3761=$A9)*('Sheet 1'!$H$2:$H$3761=1)*('Sheet 1'!$J$2:$J$3761=1),'Sheet 1'!M$2:M$3761))) and pressing ctrl+shift+enter.

The problem is whenever teh if condition results in a FALSE, the array stores a FALSE and the product is resulting in #Value. Can anyone help?

Thanks in Advance

 

[GlennUK]

Your parentheses don't match ... copy your actual formula, rather than guessing at what you did.

 

[gassarma]

Hi.. Apologies for the typo..Thanks for pointing it. pls see the actual formula below.

=subtotal(6,IF((Sheet 1'!$B$2:$B$3761=$A9)*('Sheet 1'!$H$2:$H$3761=1)*('Sheet 1'!$J$2:$J$3761=1),'Sheet 1'!M$2:M$3761))

 

[GlennUK]

I don't think your problems are caused by the array being FALSE ... I don't think that arrays get processed within SUBTOTALs that way. Could you have a column of calculations, and do the SUBTOTAL on that?

 

[Aladin Akyurek]

Alternately, i am trying to use teh following formula..


=subtotal(6,IF((Sheet 1'!$B$2:$B$3761=$A9)*('Sheet 1'!$H$2:$H$3761=1)*('Sheet 1'!$J$2:$J$3761=1),'Sheet 1'!M$2:M$3761))) and pressing ctrl+shift+enter.

The problem is whenever teh if condition results in a FALSE, the array stores a FALSE and the product is resulting in #Value. Can anyone help?

Thanks in Advance

Are you applying AutoFilter?