Use indirect to reference a cell to create formula

Question

I want to take this array formula and use Indirect to reference a cell address where it would say either MAX, MIN, AVERAGE, or SUM.

{=MAX(IF(($A$2:$A$10=$F$1)*($B$2:$B$10=$F$2),$C$2:$C$10,""))}

If the cell address E3 contained MAX then I was trying to do this:

{=INDIRECT((E3)(IF(($A$2:$A$10=$F$1)*($B$2:$B$10=$F$2),$C$2:$C$10,"")))}

That formula causes a #REF! error for the Indirect(E3) portion. Once we can get it working we can copy the formula down and if Column E says MAX, MIN, SUM, or AVERAGE the correct indirect formula will run.

Thanks for your help.

As far as I understand it, this is not possible. But as I have stated many times, I am not the smartest person here and may be proven wrong. I hope so, because then I learn something. I can get close with the AGGREGATE() formula but that will only do the MIN and the MAX. — Scott Craner
@ScottCraner I believe average and sum are part of aggregate, just not the array portion of it which is of course what you need! I thought indirect was only good for dealing with cell addresses? — Forward Ed
The closest I can get is with a helper cell and using a "NAMED" formula with evaluate in it. If you can build the formula as text in a cell, then evaluate can process that text and treat it as an excel formula. Otherwise I think you would be looking at a UDF in VBA — Forward Ed
one of the indirect variations i tried with cell E3 when i evaluated it the first step evaluated to Indirect(e3) to Indirect("Max") then to #REF! so maybe like Ed is getting at it's not being treated as a formula. Thanks for trying guys. — luckyguy73

Scott Craner Scott Craner · Accepted Answer · 2016-05-23T17:30:29

If you are open to a UDF then put this in a module attached to the workbook:

Function formulaConcat(ref As Range, form As String) As Variant
    formulaConcat = ActiveSheet.Evaluate(ref.Value & form)
End Function

Then you would need to enter the formula like:

=formulaConcat(E3,"(IF(($A$2:$A$10=$F$1)*($B$2:$B$10=$F$2),$C$2:$C$10))")

Use indirect to reference a cell to create formula

1 Answers